/// <summary>
/// 检查是否可以发货，不能发货则生成对应的采购单
/// </summary>
/// <param name="hashList">货物列表</param>
/// <param name="order_id">订单ID</param>
/// <returns></returns>
private bool checkDelivery(List <Hashtable> hashList, string order_id)
{
List <Hashtable> purchaseList = new List <Hashtable>();
AccessDB adb = new AccessDB();
for (int i = 0; i < hashList.Count; i++)
{
Hashtable ht = hashList[i];
string product_id = ht["PRODUCT_ID"].ToString();
int hw_need_number = int.Parse(ht["HW_NUMBER"].ToString());
string sql = "select ID,HW_NUMBER from OSM_STORAGE where ID = " + product_id + "";
DataTable dt = adb.SQLTableQuery(sql);
if (dt.Rows.Count == 1)
{
DataRow dr = dt.Rows[0];
int hw_storage_number = int.Parse(dr["HW_NUMBER"].ToString());
int hw_diff_number = hw_need_number - hw_storage_number;
if (hw_diff_number > 0)
{
string updateSQL = "update OSM_STORAGE set HW_NUMBER = 0 where ID = " + product_id;
adb.SQLExecute(updateSQL);
ht["HW_NUMBER"] = hw_diff_number;
purchaseList.Add(ht);
}
else
{
string updateSQL = "update OSM_STORAGE set HW_NUMBER = " + (-hw_diff_number) + " where ID = " + product_id;
adb.SQLExecute(updateSQL);
break;
}
}
else
{
ht["HW_NUMBER"] = hw_need_number;
purchaseList.Add(ht);
}
}
if (purchaseList.Count == 0)
{
//string insertSQL = "insert into OSM_DILIVERY_SHEET(ORDER_ID,DILIVERY_STATE) values(" + order_id + ",'1')";
//if (adb.SQLExecute(insertSQL))
//{
// return true;
//}
return(true);
}
else
{
int count = createPurchaseSheet(purchaseList, order_id);
MessageBox.Show("库存不足，新增" + count + "条采购记录", "消息");
return(false);
}
}
/// <summary>
/// 到货确认按钮点击事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button_OK_Click(object sender, EventArgs e)
{
if (!string.IsNullOrWhiteSpace(purchase_id) && !string.IsNullOrWhiteSpace(product_id) && !string.IsNullOrWhiteSpace(order_id))
{
if (string.IsNullOrWhiteSpace(textBox_HWNumber.Text))
{
MessageBox.Show("请填写到货数量,谢谢！", "警告");
return;
}
int number_want = int.Parse(textBox_PurchaseNumber.Text);
int number_real = int.Parse(textBox_HWNumber.Text);
if (number_want > number_real)
{
//需采购数量大于到货数量，不能确认
MessageBox.Show("到货数量小于需求数量，无法确认到货！", "警告");
return;
}
else
{
//到货数量大于等于需采购数量,改变采购记录状态，改变库存数量
AccessDB adb = new AccessDB();
string sql = "select HW_NUMBER,REAL_NUMBER from OSM_STORAGE where ID = " + product_id;
DataTable dt = adb.SQLTableQuery(sql);
DataRow dr = dt.Rows[0];
int hw_number = int.Parse(dr["HW_NUMBER"].ToString());
int real_number = int.Parse(dr["REAL_NUMBER"].ToString());
real_number += number_real;
hw_number += number_real - number_want;
string updateSQL = "update OSM_STORAGE set HW_NUMBER = " + hw_number + ", REAL_NUMBER = " + real_number + " where ID = " + product_id;
if (adb.SQLExecute(updateSQL))
{
updateSQL = "update OSM_PURCHASE_SHEET set PURCHASE_STATE = '2' where ID = " + purchase_id;
if (adb.SQLExecute(updateSQL))
{
MessageBox.Show("到货确认成功！", "消息");
fWindow.refresh_dataGridView();
checkOrder(order_id);
this.Close();
this.Dispose();
}
}
}
}
else
{
MessageBox.Show("UNEXCEPTED ERROR!", "ERROR");
}
}
/// <summary>
/// 对应报价单审核通过
/// </summary>
/// <param name="offerSheetID">报价单ID</param>
/// <param name="offerSheetCode">报价单编号</param>
private void PassOfferSheet(string offerSheetID, string offerSheetCode)
{
AccessDB adb = new AccessDB();
string query = "select sum(HW_TOTALPRICE) as REQUIRE_PAYMENT from OSM_HW where OFFERSHEET_CODE = '" + offerSheetCode + "'";
DataTable dt = adb.SQLTableQuery(query);
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
double require_payment = 0;
try
{
require_payment = double.Parse(dr["REQUIRE_PAYMENT"].ToString());
}
catch (Exception exception)
{
MessageBox.Show("该报价单没有对应的货物列表，无法通过审核！" + exception.ToString(), "警告");
return;
}
FormOSM_PayMode_Select fps = new FormOSM_PayMode_Select();
fps.setOfferForm(this);
fps.StartPosition = FormStartPosition.CenterParent;
fps.ShowDialog();
if (!string.IsNullOrWhiteSpace(pay_mode))
{
//生成订单存入数据库
string insertSQL = "insert into OSM_ORDER_SHEET(OFFERSHEET_ID,ORDER_STATE,REQUIRE_PAYMENT,ORDER_DATE,PAY_STATE,PAY_MODE) values ('";
insertSQL += offerSheetID + "','1'," + require_payment + ",#" + DateTime.Now.ToString("yyyy-MM-dd") + "#,'1','" + pay_mode + "')";
if (adb.SQLExecute(insertSQL))
{
MessageBox.Show("已将审核通过的报价单生成订单", "消息");
string sql = "update OSM_OFFER_SHEET set OFFERSHEET_STATE = '2' where ID = " + offerSheetID;
if (adb.SQLExecute(sql))
{
main_form.TSMItem_offer_aduit_Refresh();
}
}
}
else
{
MessageBox.Show("请确定付款方式！", "警告");
}
}
else
{
MessageBox.Show("该报价单没有对应的货物列表，无法通过审核！", "警告");
}
}
/// <summary>
/// 对应订单付全款
/// </summary>
/// <param name="actual_pay"></param>
private void payOrder(double actual_pay)
{
AccessDB adb = new AccessDB();
string PaySheetUpdateSQL = "update OSM_PAYMENTREMINDER_SHEET set ACHIEVE_DATE = #" + DateTime.Now.ToString("yyyy-MM-dd") + "# where ID = " + p_id;
string OrderSheetUpdateSQL = "update OSM_ORDER_SHEET set PAY_STATE = '2',ALREADY_PAYMENT = " + actual_pay + " where ID = " + order_id;
if (adb.SQLExecute(OrderSheetUpdateSQL))
{
if (adb.SQLExecute(PaySheetUpdateSQL))
{
MessageBox.Show("付款成功", "消息");
}
}
}
/// <summary>
/// 检查订单是否可以发货
/// </summary>
/// <param name="order_id"></param>
private void checkOrder(string order_id)
{
bool flag = true;
AccessDB adb = new AccessDB();
string sql = "select PURCHASE_STATE from OSM_PURCHASE_SHEET where ORDER_ID = " + order_id;
DataTable dt = adb.SQLTableQuery(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
if ("1" == dr["PURCHASE_STATE"].ToString())
{
flag = false;
}
}
if (flag)
{
string updateSQL = "update OSM_ORDER_SHEET set ORDER_STATE = '3' where ID = " + order_id;
if (adb.SQLExecute(updateSQL))
{
MessageBox.Show("对应订单已可以进入发货流程！", "消息");
//string insertSQL = "insert into OSM_DILIVERY_SHEET(ORDER_ID,DILIVERY_STATE) values(" + order_id + ",'1')";
//if (adb.SQLExecute(insertSQL))
//{
// MessageBox.Show("对应订单已可以发货,已生成发货单！", "消息");
//}
}
}
}
/// <summary>
/// 生成发货单
/// </summary>
/// <param name="order_id"></param>
private void orderDeliver(string order_id)
{
AccessDB adb = new AccessDB();
string insertSQL = "insert into OSM_DILIVERY_SHEET(ORDER_ID,DILIVERY_STATE) values(" + order_id + ",'1')";
if (adb.SQLExecute(insertSQL))
{
string updateSQL = "update OSM_ORDER_SHEET set ORDER_STATE = '4' where ID = " + order_id;
if (adb.SQLExecute(updateSQL))
{
MessageBox.Show("该订单可以发货，已生成发货单！", "消息");
queryFromOrderSheetV(dataGridView_Order, "where ORDER_STATE = '3' ");
}
}
}
/// <summary>
/// 检查订单对应货物列表
/// </summary>
/// <param name="order_id">订单ID</param>
/// <param name="offersheet_code">报价单编号</param>
private void checkOfferHW(string order_id, string offersheet_code)
{
string sql = "select PRODUCT_ID,HW_NUMBER from OSM_HW where OFFERSHEET_CODE = '" + offersheet_code + "'";
AccessDB adb = new AccessDB();
DataTable dt = adb.SQLTableQuery(sql);
List <Hashtable> hashList = new List <Hashtable>();
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
Hashtable ht = new Hashtable();
ht.Add("PRODUCT_ID", dr["PRODUCT_ID"]);
//ht.Add("HW_NAME", dr["HW_NAME"]);
//ht.Add("HW_CODE", dr["HW_CODE"]);
ht.Add("HW_NUMBER", dr["HW_NUMBER"]);
hashList.Add(ht);
}
if (checkDelivery(hashList, order_id))
{
//MessageBox.Show("根据与库存容量比对，可以发货！", "消息");
sql = "update OSM_ORDER_SHEET set ORDER_STATE = '3' where ID = " + order_id;
if (adb.SQLExecute(sql))
{
MessageBox.Show("库存充足,对应订单已可以进行发货审核！", "消息");
//查询订单视图返回结果
queryFromOrderSheetV(dataGridView_Order, "where ORDER_STATE = '1' ");
}
}
else
{
sql = "update OSM_ORDER_SHEET set ORDER_STATE = '2' where ID = " + order_id;
if (adb.SQLExecute(sql))
{
//查询订单视图返回结果
queryFromOrderSheetV(dataGridView_Order, "where ORDER_STATE = '1' ");
}
}
}
/// <summary>
/// 通过按钮点击事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button_pass_Click(object sender, EventArgs e)
{
AccessDB adb = new AccessDB();
string query = "select sum(HW_TOTALPRICE) from OSM_HW where OFFERSHEET_CODE = '" + offerSheetCode + "'";
DataTable dt = adb.SQLTableQuery(query);
if (dt.Rows.Count > 0)
{
DataRow dr = dt.Rows[0];
double require_payment = 0;
try
{
require_payment = double.Parse(dr[0].ToString());
}
catch (Exception exception)
{
MessageBox.Show("该报价单没有对应的货物列表，无法通过审核！" + exception.ToString(), "警告");
return;
}
//生成订单存入数据库
string insertSQL = "insert into OSM_ORDER_SHEET(OFFERSHEET_ID,ORDER_STATE,REQUIRE_PAYMENT,ORDER_DATE,PAY_STATE) values ('" + offerSheetID + "','1'," + require_payment + ",#" + DateTime.Now.ToString("yyyy-MM-dd") + "#,'1')";
if (adb.SQLExecute(insertSQL))
{
MessageBox.Show("已将审核通过的报价单生成订单", "消息");
string sql = "update OSM_OFFER_SHEET set OFFERSHEET_STATE = '2' where ID = " + offerSheetID;
if (adb.SQLExecute(sql))
{
main_form.TSMItem_offer_aduit_Refresh();
this.Close();
this.Dispose();
}
}
}
else
{
MessageBox.Show("该报价单没有对应的货物列表，无法通过审核！", "警告");
}
}
private void button_OK_Click(object sender, EventArgs e)
{
AccessDB adb = new AccessDB();
string updateSQL = "update OSM_DILIVERY_SHEET set ";
string orderUpdateSQL = "update OSM_ORDER_SHEET set ORDER_STATE = ";
if (state == 0)
{
//发货时间
updateSQL += " DILIVERY_DATE = #" + dateTimePicker_Delivery.Text + "#,DILIVERY_STATE='2' where ID = " + deliverySheetID;
orderUpdateSQL += " '5' where ID = " + orderID;
}
else
{
//到货时间
updateSQL += " ARRIVE_DATE = #" + dateTimePicker_Delivery.Text + "#,DILIVERY_STATE='3' where ID = " + deliverySheetID;
orderUpdateSQL += " '6' where ID = " + orderID;
}
if (adb.SQLExecute(updateSQL))
{
if (adb.SQLExecute(orderUpdateSQL))
{
MessageBox.Show("确认成功！", "消息");
this.Close();
this.Dispose();
}
else
{
MessageBox.Show("更新订单列表出错！", "错误");
this.Close();
this.Dispose();
}
}
else
{
MessageBox.Show("更新发货单列表出错！", "错误");
this.Close();
this.Dispose();
}
}
/// <summary>
/// 对应报价单审核不通过
/// </summary>
/// <param name="offerSheetID">报价单ID</param>
private void FailOfferSheet(string offerSheetID)
{
string sql = "update OSM_OFFER_SHEET set OFFERSHEET_STATE = '3' where ID = " + offerSheetID;
AccessDB adb = new AccessDB();
if (adb.SQLExecute(sql))
{
main_form.TSMItem_offer_aduit_Refresh();
}
}
/// <summary>
/// 不通过按钮点击事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button_unpass_Click(object sender, EventArgs e)
{
string sql = "update OSM_OFFER_SHEET set OFFERSHEET_STATE = '3' where ID = " + offerSheetID;
AccessDB adb = new AccessDB();
if (adb.SQLExecute(sql))
{
main_form.TSMItem_offer_aduit_Refresh();
this.Close();
this.Dispose();
}
}
/// <summary>
/// 生成收款单
/// </summary>
/// <param name="order_id">订单ID</param>
private void createPaySheet(string order_id)
{
AccessDB adb = new AccessDB();
string payListSQL = "insert into OSM_PAYMENTREMINDER_SHEET(ORDER_ID,GEN_DATE) values (" + order_id + ",#" + DateTime.Now.ToString("yyyy-MM-dd") + "#)";
if (adb.SQLExecute(payListSQL))
{
MessageBox.Show("已生成对应收款单！", "消息");
}
else
{
MessageBox.Show("生成收款单遇到错误！", "错误");
}
}
/// <summary>
/// 确认按钮点击事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button_OK_Click(object sender, EventArgs e)
{
if (textBox_old_pwd.Text == "")
{
MessageBox.Show("原密码输入框不能为空！", "警告");
return;
}
if (textBox_new_pwd.Text == "")
{
MessageBox.Show("新密码输入框不能为空！", "警告");
return;
}
AccessDB adb = new AccessDB();
string sql = "SELECT * FROM BASE_USERS WHERE USER_ID = '" + uid + "'";
DataSet ds = adb.SQLQuery(sql);
DataTable dt = ds.Tables[0];
DataRow dr = dt.Rows[0];
if (textBox_old_pwd.Text != dr["USER_PWD"].ToString() && dr["USER_PWD"] != null)
{
MessageBox.Show("原密码输入错误！", "警告");
return;
}
if (textBox_new_pwd.Text == textBox_old_pwd.Text)
{
MessageBox.Show("新密码请不要与旧密码重复", "警告");
return;
}
if (textBox_new_pwd.Text != textBox_pwd_confirm.Text)
{
MessageBox.Show("两次输入密码不一致！", "警告");
return;
}
sql = "UPDATE BASE_USERS SET USER_PWD = '" + textBox_new_pwd.Text + "' WHERE USER_ID = '" + uid + "'";
if (adb.SQLExecute(sql))
{
MessageBox.Show("密码修改成功！", "消息");
this.Close();
}
}
/// <summary>
/// 数据库新增报价单记录
/// </summary>
/// <param name="offer_sheet">报价单对象</param>
/// <returns>是否添加成功</returns>
private bool addOfferSheet(OfferSheet offer_sheet)
{
string sql = "insert into OSM_OFFER_SHEET(OFFERSHEET_CODE,GMF_ID,BJF_ID,OFFERSHEET_TYPE,OFFERSHEET_DATE,OFFERSHEET_STATE,OFFERSHEET_REGION) ";
sql += "values('" + offer_sheet.getOFFERSHEET_CODE() + "'," + offer_sheet.getGMF_ID() + "," + offer_sheet.getBJF_ID();
sql += ",'" + offer_sheet.getOFFERSHEET_TYPE() + "','" + offer_sheet.getOFFERSHEET_DATE() + "','";
sql += offer_sheet.getOFFERSHEET_STATE() + "','" + offer_sheet.getOFFERSHEET_REGION() + "')";
AccessDB adb = new AccessDB();
bool isExecuteSucc = adb.SQLExecute(sql);
if (isExecuteSucc)
{
MessageBox.Show("添加成功！", "消息");
}
else
{
MessageBox.Show("添加失败！", "消息");
}
return(isExecuteSucc);
}
/// <summary>
/// 实现数据库插入报价方数据
/// </summary>
/// <param name="seller"></param>
/// <returns></returns>
private bool addSeller(Seller seller)
{
string sql = "insert into OSM_BJF(COM_NAME,ADDR,CONTACT,PHONE,ZIP_CODE,FAX,EMAIL,COMMENT) ";
sql += " values('" + seller.getCOM_NAME() + "','" + seller.getADDR() + "','";
sql += seller.getCONTACT() + "','" + seller.getPHONE() + "','" + seller.getZIP_CODE();
sql += "','" + seller.getFAX() + "','" + seller.getEMAIL() + "','" + seller.getCOMMENT() + "')";
AccessDB adb = new AccessDB();
bool isExecuteSucc = adb.SQLExecute(sql);
if (isExecuteSucc)
{
MessageBox.Show("添加成功！", "消息");
}
else
{
MessageBox.Show("添加失败！", "消息");
}
return(isExecuteSucc);
}
/// <summary>
/// 实现数据库插入货物数据
/// </summary>
/// <param name="hw"></param>
/// <returns></returns>
private bool addHW(HW hw)
{
string sql = "insert into OSM_HW(OFFERSHEET_CODE,PRODUCT_ID,HW_NUMBER,HW_PRICE,HW_TOTALPRICE,COMMENT) ";
sql += "values('" + hw.getOFFERSHEET_CODE() + "'," + hw.getPRODUCT_ID() + ",";
sql += hw.getHW_NUMBER().ToString() + "," + hw.getHW_PRICE().ToString();
sql += "," + hw.getHW_TOTALPRICE().ToString() + ",'" + hw.getCOMMENT() + "')";
AccessDB adb = new AccessDB();
bool isExecuteSucc = adb.SQLExecute(sql);
if (isExecuteSucc)
{
MessageBox.Show("添加成功！", "消息");
}
else
{
MessageBox.Show("添加失败！", "消息");
}
return(isExecuteSucc);
}
/// <summary>
/// 数据库更新报价单记录
/// </summary>
/// <param name="offer_sheet"></param>
/// <returns>是否更新成功</returns>
private bool updateOfferSheet(OfferSheet offer_sheet)
{
string sql = "update OSM_OFFER_SHEET set OFFERSHEET_CODE = '" + offer_sheet.getOFFERSHEET_CODE() + "',";
sql += "GMF_ID=" + offer_sheet.getGMF_ID().ToString() + ",BJF_ID=" + offer_sheet.getBJF_ID() + ",";
sql += "OFFERSHEET_TYPE='" + offer_sheet.getOFFERSHEET_TYPE() + "',OFFERSHEET_DATE = #" + offer_sheet.getOFFERSHEET_DATE() + "#,";
sql += "OFFERSHEET_STATE='" + offer_sheet.getOFFERSHEET_STATE() + "',";
sql += "OFFERSHEET_REGION = '" + offer_sheet.getOFFERSHEET_REGION() + "' ";
sql += "where ID = " + offer_sheet.getID().ToString();
AccessDB adb = new AccessDB();
bool isExecuteSucc = adb.SQLExecute(sql);
if (isExecuteSucc)
{
MessageBox.Show("更新成功！", "消息");
}
else
{
MessageBox.Show("更新失败！", "消息");
}
return(isExecuteSucc);
}
/// <summary>
/// 根据得到的hashList货物列表生成采购单
/// </summary>
/// <param name="purchaseList">采购货物列表</param>
/// <param name="order_id">订单</param>
/// <returns>新增采购记录数量</returns>
private int createPurchaseSheet(List <Hashtable> purchaseList, string order_id)
{
AccessDB adb = new AccessDB();
int count = 0;
string insertSql = "insert into OSM_PURCHASE_SHEET(ORDER_ID,PRODUCT_ID,PURCHASE_NUMBER,PURCHASE_STATE,INIT_DATE) values (";
for (int i = 0; i < purchaseList.Count; i++)
{
Hashtable ht = purchaseList[i];
string valueString = order_id + ",";
valueString += ht["PRODUCT_ID"].ToString() + ",";
//valueString += "'" + ht["HW_NAME"].ToString() + "',";
//valueString += "'" + ht["HW_CODE"].ToString() + "',";
valueString += ht["HW_NUMBER"].ToString() + ",'1',#" + DateTime.Now.ToString("yyyy-MM-dd") + "#)";
string sql = insertSql + valueString;
if (adb.SQLExecute(sql))
{
count++;
}
}
return(count);
}
private void button_OK_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(textBox_Name.Text))
{
MessageBox.Show("产品名称不能为空！", "警告");
return;
}
if (string.IsNullOrWhiteSpace(textBox_Code.Text))
{
MessageBox.Show("产品代码不能为空！", "警告");
return;
}
if (comboBox_Type.SelectedIndex == -1)
{
MessageBox.Show("所属设备不能为空！", "警告");
return;
}
AccessDB adb = new AccessDB();
KeyValuePair <string, string> item = (KeyValuePair <string, string>)(comboBox_Type.SelectedItem);
string insertSQL = "INSERT INTO OSM_STORAGE(HW_NAME,HW_CODE,HW_TYPE,COMMENT) VALUES (";
insertSQL += "'" + textBox_Name.Text + "','" + textBox_Code.Text + "','" + item.Key + "','" + textBox_Comment.Text + "')";
if (adb.SQLExecute(insertSQL))
{
MessageBox.Show("添加成功！", "消息");
HWWindow.refresh_product_comboBox();
this.Close();
this.Dispose();
}
}
