private void Form2_Load(object sender, EventArgs e)
{
string sql = "select distinct cycle from db2admin.view_quantities where year>'2019年' order by cycle desc";
ExcelTodb2 dt = new ExcelTodb2();
string [][] item2 = dt.DataTableToArray(sql);
comboBox1.DataSource = dt.ArrayToItem(item2);
}
/* public string[] getxiaxia(string[][] st1)
* {
*
* Datatabletoarr sp = new Datatabletoarr();
* string[] st2 = new string[st1.Length ];
* st2=sp.Toarr1(st1);
* if (st2.Length == 0 || st2 == null)
* {
* return null;
* }
* else
* {
* return st2;
* }
* }
* /* public string[][] Getdd(string sql)
* {
* DataTable dl = new DataTable();
* Getdata gt = new Getdata();
* dl = gt.Getdat_db2(sql);
* string[][] st = null;
* Datatabletoarr st1 = new Datatabletoarr();
* st = st1.Toarry(dl);
* return st;
*
*
* }*/
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
string sql = "select distinct productdesc,productcode from db2admin.view_quantities where cycle='" + comboBox1.Text + "' order by productcode";
ExcelTodb2 dt = new ExcelTodb2();
DataTable tb = new DataTable();
Item1 = dt.DataTableToArray(sql);
Item = dt.ArrayToItem(Item1);
comboBox2.DataSource = Item;
}
private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
{
int x = Item.ToList().IndexOf(comboBox2.Text);
string product = Item1 [x][1];
string cyc = cycformat(comboBox1.Text);
string sql = "SELECT B.DANGWEI2 档位,COALESCE(SUM(SW.SUPPLY_COUNT), 0) 投放户数,COALESCE(SUM(SW.ORDER_COUNT), 0) 订购户数,COALESCE(SUM(SW.FULL_ORDER_COUNT), 0) 订足户数," +
"COALESCE(SUM(SW.QTY_SUPPLY), 0) 投放量,COALESCE(SUM(SW.QTY_ORDER), 0) 订购量,COALESCE(SUM(SW.QTY_NEED), 0) 需求量," +
"CASE WHEN COALESCE(SUM(SW.QTY_SUPPLY), 0) > 0 THEN COALESCE(SUM(SW.QTY_SUPPLY), 0) / COALESCE(SUM(SW.SUPPLY_COUNT), 0) ELSE 0 END 投放条数," +
"CAST(ROUND((CASE WHEN COALESCE(SUM(SW.QTY_SUPPLY), 0) > 0 THEN COALESCE(SUM(SW.QTY_ORDER), 0) * 100.00 / COALESCE(SUM(SW.QTY_SUPPLY), 0)" +
"ELSE 0 END), 2) AS DECIMAL(18, 2)) 订足率,COALESCE(SUM(SW.ORDER_COUNT), 0) - COALESCE(SUM(SW.FULL_ORDER_COUNT), 0) 订足户差," +
"CASE WHEN COALESCE(SUM(SW.QTY_SUPPLY), 0) > 0 THEN COALESCE(SUM(SW.QTY_SUPPLY), 0) / COALESCE(SUM(SW.SUPPLY_COUNT), 0) ELSE 0 END 投放调整," +
"CAST(ROUND((CASE WHEN COALESCE(SUM(SW.QTY_NEED), 0) > 0 THEN COALESCE(SUM(SW.QTY_ORDER), 0) * 100.00 / COALESCE(SUM(SW.QTY_NEED), 0)" +
"ELSE 0 END), 2) AS DECIMAL(18, 2)) 需求满足率 FROM DB2ADMIN.CUSTDANGWEIMAPPING B left OUTER JOIN DB2ADMIN.CUSTTYPE_ITEM_SW SW " +
"ON SW.CUST_TYPE = B.DANGWEI2 AND SW.CUST_TYPE != 'ZZ' AND SW.TYPE_KIND = '301FD' AND SW.ITEM_ID = '" + product + "' AND SW.DATE1 = '" + cyc + "'" +
"GROUP BY B.DANGWEI2 ORDER BY B.DANGWEI2 DESC ";
ExcelTodb2 gt = new ExcelTodb2();
dt = gt.SqlToDataTbale(sql);
this.dataRefresh();
}
private void button1_Click(object sender, EventArgs e)
{
//向父窗体传递新增周期参数
weituo2(pam);
// _ = new DataSet();
//ds = ExcelTodb2.ReadExcel(label2.Text);
//查询是否已经导入过文件周期的数据
ExcelTodb2 tt = new ExcelTodb2();
string sql = string.Format("select distinct cyclname,cyclcode from db2admin.sales_cycle where cyclcode='{0}'", label9.Text);
// DataSet ds = tt.UpToDb2(sql);
string[] st = tt.ArrayToItem(tt.DataTableToArray(sql));
if (st == null)
{
MessageBox.Show("数据库查询错误，请联系管理员");
}
else if (st.Length > 0)
{
MessageBox.Show("数据库已有相关记录");
}
else
{
foreach (string tex in path)
{
tt.UpToDb2(tex);
}
}
//MessageBox.Show(ds.Tables.Count.ToString ());
//dataGridView1.DataSource =ds.Tables ["pam"];
// dataGridView1 .
// DataRow [] dr = ds.Tables[0].Select("F5 like '%标准%'");
this.Dispose();
}
private void button2_Click(object sender, EventArgs e)
{
button1.Enabled = false;
int num = 0;
int num1 = 0;
OpenFileDialog f = new OpenFileDialog();
f.Filter = "Worksheet Files (*.xls)|*.xls||";
f.Title = "选择投放文件，可以多选哦~~~~~";
f.Multiselect = true;
if (f.ShowDialog() == DialogResult.OK)
{
path = f.FileNames;
if (path.Length > 1)
{
foreach (string path1 in path)
{
if (path1.IndexOf("投放") > 0)
{
num += 1;
}
}
if (path.Length != num)
{
num = 0;
MessageBox.Show("请选择投放文件");
}
else
{
string path2 = path[0].Substring(0, path[0].IndexOf("放"));
foreach (string path1 in path)
{
if (!string.Equals(path2, path1.Substring(0, path[0].IndexOf("放"))))
{
num1 += 1;
}
}
if (num1 > 0)
{
num1 = 0;
MessageBox.Show("请选择同一投放周期的文件");
}
else
{
//label2.Text = path[0];
ExcelTodb2 dt = new ExcelTodb2();
pam = dt.Excel_cyc(path[0]);
dt = null;
for (int x = 0; x < pam.Length; x++)
{
string st = string.Format("label{0}", x + 9);
foreach (Control cr1 in Controls)
{
if (cr1.Name == st)
{
cr1.Text = pam[x];
}
}
button1.Enabled = true;
}
}
}
}
else
{
MessageBox.Show("请选择两个或两个以上的策略文件");
}
}
}