private void import_ribao_table(string outlet, ribao_t r)
{
string conn_str = "server=rds3vt9mpg9qj5biy748a.mysql.rds.aliyuncs.com;User Id=fsradmin;password=fsradmin;Database=fsr";
MySqlConnection conn = new MySqlConnection(conn_str);
string insert_str = "insert into ribao(outlet, branch, brand, area, person, meal_income, member_income, outsale_income, credit_income, total_income, total_discount, date) values(\'{0}\', \'{1}\', \'{2}\', \'{3}\', {4}, {5}, {6}, {7}, {8}, {9}, {10}, \'{11}\')";
MySqlCommand cmd = new MySqlCommand(String.Format(insert_str, outlet, r.branch, r.band, r.area, r.person, r.get_meal_income(), r.get_member_card_income(),
r.get_outsale_income(), r.get_credit_gift_income(), r.get_income(), r.get_discount(), dateText.Text), conn);
//MessageBox.Show(cmd.CommandText);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
string sql_dis_str = "insert into ribao_dis(outlet, branch, brand, area, item_dis, cat_dis, order_dis, rounding_dis, free_dis, credit_dis, recep_dis, card_dis, ticket_dis, total_dis, date) values(\'{0}\', \'{1}\', \'{2}\', \'{3}\', {4}, {5}, {6}, {7}, {8}, {9}, {10}, {11}, {12}, {13}, \'{14}\')";
MySqlCommand cmd_dis = new MySqlCommand(String.Format(sql_dis_str, outlet, r.branch, r.band, r.area, r.item_dis, r.cat_dis + r.order_dis, r.order_dis, r.rounding_amt, r.free_dis, r.credit_dis,r.recep_dis, r.card_dis, r.ticket_dis, r.get_discount(), dateText.Text), conn);
conn.Open();
cmd_dis.ExecuteNonQuery();
conn.Close();
/*
SqlConnection sql_conn;
SqlCommand sql_cmd;
SqlDataAdapter da;
string strConnection = "user id=sa;password=wang419420;";
strConnection += "initial catalog=" + dbname + ";Server=192.168.3.101\\foodsd;";
strConnection += "Connect Timeout=30";
sql_conn = new SqlConnection(strConnection);
string sql = String.Format("insert into dbo.ribao values(\'{0}\', \'{1}\', \'{2}\', \'{3}\', {4}, {5}, {6}, {7}, {8}, {9}, {10}, \'{11}\')",
outlet, r.branch, r.band, r.area, r.person, r.get_meal_income(), r.get_member_card_income(),
r.get_outsale_income(), r.get_credit_gift_income(), r.get_income(), r.get_discount(), dateText.Text);
sql_cmd = new SqlCommand(sql);
sql_cmd.Connection = sql_conn;
sql_cmd.Connection.Open();
sql_cmd.ExecuteNonQuery();
sql_cmd.Connection.Close();
*/
return;
}
private void import_daily_ribao()
{
ribao_map.Clear();
//delete_ribao_table();
DataTable total_dt = get_datatable(String.Format(sql_map["get_total"], dateText.Text));
DataTable member_card_dt = get_datatable(String.Format(sql_map["get_member_card"], dateText.Text));
DataTable credit_gift_dt = get_datatable(String.Format(sql_map["get_credit_gift"], dateText.Text));
DataTable outsale_credit_dt = get_datatable(String.Format(sql_map["get_outsale_credit"], dateText.Text));
for (int i = 0; i < total_dt.Rows.Count; i++)
{
ribao_t ribao = new ribao_t(Convert.ToInt32(total_dt.Rows[i][5]), Convert.ToDouble(total_dt.Rows[i][1]),
Convert.ToDouble(total_dt.Rows[i][2]), Convert.ToDouble(total_dt.Rows[i][3]),
Convert.ToDouble(total_dt.Rows[i][4]), Convert.ToDouble(total_dt.Rows[i][6]),
Convert.ToDouble(total_dt.Rows[i][7]), Convert.ToDouble(total_dt.Rows[i][8]));
ribao_map.Add(total_dt.Rows[i][0].ToString().Trim(), ribao);
}
DataTable branch_dt = get_datatable(String.Format(sql_map["get_branch"], dateText.Text));
for (int i = 0; i < branch_dt.Rows.Count; i++) {
foreach (string key in ribao_map.Keys)
if (branch_dt.Rows[i][0].ToString().Trim() == key)
{
ribao_map[key].branch = branch_dt.Rows[i][1].ToString().Trim();
ribao_map[key].area = branch_dt.Rows[i][2].ToString().Trim();
ribao_map[key].band = branch_dt.Rows[i][3].ToString().Trim();
}
}
DataTable free_dis_dt = get_datatable(String.Format(sql_map["get_free_dis"], dateText.Text));
for (int i = 0; i < free_dis_dt.Rows.Count; i++)
ribao_map[free_dis_dt.Rows[i][0].ToString()].free_dis = Convert.ToDouble(free_dis_dt.Rows[i][1]);
DataTable credit_dis_dt = get_datatable(String.Format(sql_map["get_credit_dis"], dateText.Text));
for (int i = 0; i < credit_dis_dt.Rows.Count; i++)
ribao_map[credit_dis_dt.Rows[i][0].ToString()].credit_dis = Convert.ToDouble(credit_dis_dt.Rows[i][1]);
DataTable recep_dis_dt = get_datatable(String.Format(sql_map["get_recep_dis"], dateText.Text));
for (int i = 0; i < recep_dis_dt.Rows.Count; i++)
ribao_map[recep_dis_dt.Rows[i][0].ToString()].recep_dis = Convert.ToDouble(recep_dis_dt.Rows[i][1]);
DataTable card_dis_dt = get_datatable(String.Format(sql_map["get_card_dis"], dateText.Text));
for (int i = 0; i < card_dis_dt.Rows.Count; i++)
ribao_map[card_dis_dt.Rows[i][0].ToString()].card_dis = Convert.ToDouble(card_dis_dt.Rows[i][1]);
DataTable ticket_dis_dt = get_datatable(String.Format(sql_map["get_ticket_dis"], dateText.Text));
for (int i = 0; i < ticket_dis_dt.Rows.Count; i++)
{
ribao_map[ticket_dis_dt.Rows[i][0].ToString()].ticket_dis = Convert.ToDouble(ticket_dis_dt.Rows[i][1]);
DataTable ticket_dis_dt_2 = get_datatable(String.Format(sql_map["get_ticket_dis_2"], dateText.Text, ticket_dis_dt.Rows[i][0].ToString()));
for (int j = 0; j < ticket_dis_dt_2.Rows.Count; j++)
{
if (Convert.ToInt16(ticket_dis_dt_2.Rows[j][1]) > 1)
{
ribao_map[ticket_dis_dt.Rows[i][0].ToString()].ticket_dis -= Convert.ToDouble(ticket_dis_dt_2.Rows[j][2]);
}
}
}
DataTable member_card_sale_dt = get_datatable(String.Format(sql_map["get_order_non_free"], dateText.Text));
for (int i = 0; i < member_card_sale_dt.Rows.Count; i++)
{
for (int j = 0; j < member_card_dt.Rows.Count; j++)
{
if (member_card_dt.Rows[j][0].ToString().Trim() == member_card_sale_dt.Rows[i][0].ToString().Trim() &&
member_card_dt.Rows[j][1].ToString().Trim() == member_card_sale_dt.Rows[i][1].ToString().Trim())
{
ribao_map[member_card_sale_dt.Rows[i][2].ToString()].member_card_sale += Convert.ToDouble(member_card_sale_dt.Rows[i][3]);
break;
}
}
}
DataTable member_card_free_dt = get_datatable(String.Format(sql_map["get_order_free"], dateText.Text));
for (int i = 0; i < member_card_free_dt.Rows.Count; i++)
{
for (int j = 0; j < member_card_dt.Rows.Count; j++)
{
if (member_card_dt.Rows[j][0].ToString().Trim() == member_card_free_dt.Rows[i][0].ToString().Trim() &&
member_card_dt.Rows[j][1].ToString().Trim() == member_card_free_dt.Rows[i][1].ToString().Trim())
{
ribao_map[member_card_free_dt.Rows[i][2].ToString()].member_card_free += Convert.ToDouble(member_card_free_dt.Rows[i][3]);
break;
}
}
}
DataTable credit_gift_sale_dt = get_datatable(String.Format(sql_map["get_order_non_free"], dateText.Text));
for (int i = 0; i < credit_gift_sale_dt.Rows.Count; i++)
{
for (int j = 0; j < credit_gift_dt.Rows.Count; j++)
{
if (credit_gift_dt.Rows[j][0].ToString().Trim() == credit_gift_sale_dt.Rows[i][0].ToString().Trim() &&
credit_gift_dt.Rows[j][1].ToString().Trim() == credit_gift_sale_dt.Rows[i][1].ToString().Trim())
{
ribao_map[credit_gift_sale_dt.Rows[i][2].ToString()].credit_gift_sale += Convert.ToDouble(credit_gift_sale_dt.Rows[i][3]);
break;
}
}
}
DataTable credit_gift_free_dt = get_datatable(String.Format(sql_map["get_order_free"], dateText.Text));
for (int i = 0; i < credit_gift_free_dt.Rows.Count; i++)
{
for (int j = 0; j < credit_gift_dt.Rows.Count; j++)
{
if (credit_gift_dt.Rows[j][0].ToString().Trim() == credit_gift_free_dt.Rows[i][0].ToString().Trim() &&
credit_gift_dt.Rows[j][1].ToString().Trim() == credit_gift_free_dt.Rows[i][1].ToString().Trim())
{
ribao_map[credit_gift_free_dt.Rows[i][2].ToString()].credit_gift_free += Convert.ToDouble(credit_gift_free_dt.Rows[i][3]);
break;
}
}
}
DataTable outsale_credit_sale_dt = get_datatable(String.Format(sql_map["get_order_non_free"], dateText.Text));
for (int i = 0; i < outsale_credit_sale_dt.Rows.Count; i++)
{
for (int j = 0; j < outsale_credit_dt.Rows.Count; j++)
{
if (outsale_credit_dt.Rows[j][0].ToString().Trim() == outsale_credit_sale_dt.Rows[i][0].ToString().Trim() &&
outsale_credit_dt.Rows[j][1].ToString().Trim() == outsale_credit_sale_dt.Rows[i][1].ToString().Trim())
{
ribao_map[outsale_credit_sale_dt.Rows[i][2].ToString()].outsale_credit_sale += Convert.ToDouble(outsale_credit_sale_dt.Rows[i][3]);
break;
}
}
}
DataTable outsale_credit_free_dt = get_datatable(String.Format(sql_map["get_order_free"], dateText.Text));
for (int i = 0; i < outsale_credit_free_dt.Rows.Count; i++)
{
for (int j = 0; j < outsale_credit_dt.Rows.Count; j++)
{
if (outsale_credit_dt.Rows[j][0].ToString().Trim() == outsale_credit_free_dt.Rows[i][0].ToString().Trim() &&
outsale_credit_dt.Rows[j][1].ToString().Trim() == outsale_credit_free_dt.Rows[i][1].ToString().Trim())
{
ribao_map[outsale_credit_free_dt.Rows[i][2].ToString()].outsale_credit_free += Convert.ToDouble(outsale_credit_free_dt.Rows[i][3]);
break;
}
}
}
foreach (string key in ribao_map.Keys)
{
//import_ribao_table(key, ribao_map[key]);
/*MessageBox.Show(String.Format("{0}: {1} {2} {3} {4} {5} {6} {7}", key, ribao_map[key].branch,
ribao_map[key].person, ribao_map[key].get_meal_income(), ribao_map[key].get_member_card_income(),
ribao_map[key].item_dis, ribao_map[key].cat_dis, ribao_map[key].order_dis
));*/
}
//MessageBox.Show("日报导入完成");
}
