void UCSalePlanView_SaveEvent(object sender, EventArgs e) { try { gvPurchasePlanList.EndEdit(); List<SysSQLString> listSql = new List<SysSQLString>(); SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; Dictionary<string, string> dic = new Dictionary<string, string>();//参数 string sql1 = string.Format(@" Update tb_parts_sale_plan Set is_suspend=@is_suspend,suspend_reason=@suspend_reason,update_by=@update_by, update_name=@update_name,update_time=@update_time,operators=@operators,operator_name=@operator_name where sale_plan_id=@sale_plan_id;"); dic.Add("is_suspend", chkis_suspend.Checked ? "0" : "1");//选中(中止):0,未选中(不中止):1 dic.Add("suspend_reason", txtsuspend_reason.Caption.Trim()); dic.Add("update_by", GlobalStaticObj.UserID); dic.Add("update_name", GlobalStaticObj.UserName); dic.Add("update_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString()); dic.Add("operators", GlobalStaticObj.UserID); dic.Add("operator_name", GlobalStaticObj.UserName); dic.Add("sale_plan_id", planId); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); foreach (DataGridViewRow dr in gvPurchasePlanList.Rows) { string is_suspend = "1"; if (dr.Cells["is_suspend"].Value == null) { is_suspend = "1"; } if ((bool)dr.Cells["is_suspend"].EditedFormattedValue) { is_suspend = "0"; } else { is_suspend = "1"; } sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); dic.Add("is_suspend", is_suspend); dic.Add("sale_plan_id", planId); dic.Add("parts_code", dr.Cells["parts_code"].Value.ToString()); string sql2 = "Update tb_parts_sale_plan_p set is_suspend=@is_suspend where sale_plan_id=@sale_plan_id and parts_code=@parts_code;"; sysStringSql.sqlString = sql2; sysStringSql.Param = dic; listSql.Add(sysStringSql); } if (DBHelper.BatchExeSQLStringMultiByTrans("修改采购计划单", listSql)) { MessageBoxEx.Show("保存成功!"); uc.BindgvSalePlanList(); deleteMenuByTag(this.Tag.ToString(), uc.Name); } else { MessageBoxEx.Show("保存失败!"); } } catch (Exception ex) { MessageBoxEx.Show("操作失败!"); } }
protected override bool LockDocument(string ids) { ids = ids.Replace(",", "','"); List<SysSQLString> listSql = new List<SysSQLString>(); SysSQLString saleBilling = new SysSQLString();//销售开单 saleBilling.cmdType = CommandType.Text; saleBilling.Param = new Dictionary<string, string>(); saleBilling.sqlString =string.Format( "update tb_parts_sale_billing set is_lock=@is_lock where sale_billing_id in ({0})",ids); saleBilling.Param.Add("is_lock", ((int)DataSources.EnumImportStaus.LOCK).ToString()); //saleBilling.Param.Add("id", ids); listSql.Add(saleBilling); SysSQLString maintainSql = new SysSQLString();//维修结算单 maintainSql.cmdType = CommandType.Text; maintainSql.sqlString =string.Format( "update tb_maintain_settlement_info set is_lock=@is_lock where settlement_id in ({0})",ids); maintainSql.Param = new Dictionary<string, string>(); maintainSql.Param.Add("is_lock", ((int)DataSources.EnumImportStaus.LOCK).ToString()); //maintainSql.Param.Add("id", ids); listSql.Add(maintainSql); SysSQLString threeSql = new SysSQLString();//三包服务单 threeSql.cmdType = CommandType.Text; threeSql.sqlString = string.Format("update tb_maintain_three_guaranty_settlement set is_lock=@is_lock where st_id in ({0})", ids); threeSql.Param = new Dictionary<string, string>(); threeSql.Param.Add("is_lock", ((int)DataSources.EnumImportStaus.LOCK).ToString()); //threeSql.Param.Add("id", ids); listSql.Add(threeSql); return DBHelper.BatchExeSQLStringMultiByTrans("锁定应收单据", listSql); }
//确认,保存到数据库并关闭当前窗体 private void btnOK_Click(object sender, EventArgs e) { dgvSet.EndEdit(); List<SysSQLString> listSql = new List<SysSQLString>(); bool isCheck = true; foreach (DataGridViewRow dgvr in dgvSet.Rows) { string width = CommonCtrl.IsNullToString(dgvr.Cells[colSetWidth.Name].Value); if (width.Length == 0) { isCheck = false; dgvSet.CurrentCell = dgvr.Cells[colSetWidth.Name]; break; } SysSQLString sql = new SysSQLString(); sql.cmdType = CommandType.Text; sql.sqlString = @"update tb_report_set set set_width=@set_width,is_show=@is_show,is_print=@is_print where set_id=@set_id;"; sql.Param = new Dictionary<string, string>(); sql.Param.Add("set_id", dgvr.Cells[colSetID.Name].Value.ToString()); sql.Param.Add("set_width", dgvr.Cells[colSetWidth.Name].Value.ToString()); sql.Param.Add("is_show", Convert.ToBoolean(dgvr.Cells[colIsShow.Name].Value)?"1":"0"); sql.Param.Add("is_print", Convert.ToBoolean(dgvr.Cells[colIsPrint.Name].Value)?"1":"0"); listSql.Add(sql); } if (!isCheck) { return; } if (DBHelper.BatchExeSQLStringMultiByTrans("", listSql)) { this.DialogResult = DialogResult.OK; this.Close(); } }
/// <summary> 激活/作废 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> void UCPurchasePlanOrderView_InvalidOrActivationEvent(object sender, EventArgs e) { string strmsg = string.Empty; List<SysSQLString> listSql = new List<SysSQLString>(); SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; Dictionary<string, string> dic = new Dictionary<string, string>();//参数 dic.Add("purchase_order_yt_id", purchase_order_yt_id);//单据ID dic.Add("update_by", GlobalStaticObj.UserID);//修改人Id dic.Add("update_name", GlobalStaticObj.UserName);//修改人姓名 dic.Add("update_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());//修改时间 if (orderstatus != Convert.ToInt32(DataSources.EnumAuditStatus.Invalid).ToString()) { strmsg = "作废"; dic.Add("order_status", Convert.ToInt32(DataSources.EnumAuditStatus.Invalid).ToString());//单据状态编号 dic.Add("order_status_name", DataSources.GetDescription(DataSources.EnumAuditStatus.Invalid, true));//单据状态名称 } else { strmsg = "激活"; string order_status = string.Empty; string order_status_name = string.Empty; DataTable dvt = DBHelper.GetTable("获得宇通采购订单的前一个状态", "tb_parts_purchase_order_2_BackUp", "order_status,order_status_name", "purchase_order_yt_id='" + purchase_order_yt_id + "'", "", "order by update_time desc"); if (dvt != null && dvt.Rows.Count > 0) { DataRow dr = dvt.Rows[0]; order_status = CommonCtrl.IsNullToString(dr["order_status"]); if (order_status == Convert.ToInt32(DataSources.EnumAuditStatus.Invalid).ToString()) { DataRow dr1 = dvt.Rows[1]; order_status = CommonCtrl.IsNullToString(dr1["order_status"]); order_status_name = CommonCtrl.IsNullToString(dr1["order_status_name"]); } } order_status = !string.IsNullOrEmpty(order_status) ? order_status : Convert.ToInt32(DataSources.EnumAuditStatus.DRAFT).ToString(); if (order_status == Convert.ToInt32(DataSources.EnumAuditStatus.NOTAUDIT).ToString()) { order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.NOTAUDIT, true); } else if (order_status == Convert.ToInt32(DataSources.EnumAuditStatus.DRAFT).ToString()) { order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.DRAFT, true); } dic.Add("order_status", order_status);//单据状态 dic.Add("order_status_name", order_status_name);//单据状态名称 } sysStringSql.sqlString = "update tb_parts_purchase_order_2 set order_status=@order_status,order_status_name=@order_status_name,update_by=@update_by,update_name=@update_name,update_time=@update_time where purchase_order_yt_id=@purchase_order_yt_id"; sysStringSql.Param = dic; listSql.Add(sysStringSql); if (MessageBoxEx.Show("确认要" + strmsg + "吗?", "提示", MessageBoxButtons.OKCancel) != DialogResult.OK) { return; } if (DBHelper.BatchExeSQLStringMultiByTrans("更新单据状态为" + strmsg + "", listSql)) { MessageBoxEx.Show("" + strmsg + "成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); uc.BindgvYTPurchaseOrderList(); deleteMenuByTag(this.Tag.ToString(), uc.Name); } else { MessageBoxEx.Show("" + strmsg + "失败!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information); } }
public static SysSQLString GetPurchaseOrder(string statusName, string importStatus, string ids) { SysSQLString sql = new SysSQLString(); sql.cmdType = CommandType.Text; sql.Param = new Dictionary<string, string>(); sql.sqlString = string.Format("update tb_parts_purchase_order set {0}='{1}' where order_id in ({2});", statusName, importStatus, ids); return sql; }
/// <summary> /// 三包结算单 /// </summary> /// <param name="statusName"></param> /// <param name="importStatus"></param> /// <param name="ids"></param> /// <returns></returns> public static SysSQLString GetMaintainThree(string statusName, string importStatus, string ids) { SysSQLString sql = new SysSQLString(); sql.cmdType = CommandType.Text; sql.Param = new Dictionary<string, string>(); sql.sqlString = string.Format("update tb_maintain_three_guaranty_settlement set {0}='{1}' where st_id in ({2});", statusName, importStatus, ids); return sql; }
/// <summary> /// 销售开单 /// </summary> /// <param name="statusName"></param> /// <param name="importStatus"></param> /// <param name="ids"></param> /// <returns></returns> public static SysSQLString GetSaleBilling(string statusName, string importStatus, string ids) { SysSQLString sql = new SysSQLString(); sql.cmdType = CommandType.Text; sql.Param = new Dictionary<string, string>(); sql.sqlString = string.Format("update tb_parts_sale_billing set {0}='{1}' where sale_billing_id in ({2});", statusName, importStatus, ids); return sql; }
/// <summary> /// 添加收付款sql /// </summary> /// <param name="list"></param> static void AddReceivableSqlString(List<SysSQLString> list, tb_bill_receivable bill) { SysSQLString sql = new SysSQLString(); sql.cmdType = CommandType.Text; sql.Param = new Dictionary<string, string>(); sql.Param.Add("payable_single_id", bill.payable_single_id); sql.Param.Add("order_num", bill.order_num);//单号 sql.Param.Add("order_date", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());//时间 sql.Param.Add("order_status", ((int)DataSources.EnumAuditStatus.SUBMIT).ToString());//单据状态 sql.Param.Add("order_type", bill.order_type.ToString());//单据类型 sql.Param.Add("cust_id", bill.cust_id); //sql.Param.Add("cust_code", bill.cust_code);//往来单位 //sql.Param.Add("cust_name", bill.cust_name);// sql.Param.Add("payment_type", bill.payment_type.ToString());//收付款类型 sql.Param.Add("payment_money", bill.payment_money.ToString()); sql.Param.Add("dealings_balance", bill.dealings_balance==null ?null :bill.dealings_balance.ToString()); //sql.Param.Add("bank_of_deposit", bill.bank_of_deposit);//开户银行 //sql.Param.Add("bank_account", bill.bank_account);//银行账户 sql.Param.Add("org_id", bill.org_id);//部门 sql.Param.Add("handle", GlobalStaticObj.UserID);//经办人 sql.Param.Add("operator", GlobalStaticObj.UserID);//操作人 //sql.Param.Add("remark", bill.remark); sql.Param.Add("create_by", GlobalStaticObj.UserID); sql.Param.Add("create_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString()); sql.Param.Add("status", ((int)DataSources.EnumStatus.Start).ToString()); sql.Param.Add("enable_flag", ((int)DataSources.EnumEnableFlag.USING).ToString()); // sql.sqlString = @"INSERT INTO [tb_bill_receivable] // (payable_single_id,order_num ,order_date,order_status,order_type,cust_id,cust_code,cust_name,payment_type,payment_money,dealings_balance //,bank_of_deposit,bank_account,org_id,handle,operator,create_by,create_time,status,enable_flag,remark) // VALUES // (@payable_single_id,@order_num,@order_date,@order_status,@order_type,@cust_id,@cust_code,@cust_name,@payment_type,@payment_money,@dealings_balance //,@bank_of_deposit,@bank_account,@org_id,@handle,@operator,@create_by,@create_time,@status,@enable_flag,@remark);"; if (bill.order_type == 0) { sql.sqlString = @"INSERT INTO [tb_bill_receivable] (payable_single_id,order_num ,order_date,order_status,order_type,cust_id,cust_code,cust_name,payment_type,payment_money,dealings_balance ,bank_of_deposit,bank_account,org_id,handle,operator,create_by,create_time,status,enable_flag) select @payable_single_id,@order_num,@order_date,@order_status,@order_type,@cust_id,cust_code,cust_name,@payment_type,@payment_money,@dealings_balance, bank_account_person,bank_account,@org_id,@handle,@operator,@create_by,@create_time,@status,@enable_flag from tb_customer where cust_id=@cust_id"; } else { sql.sqlString = @"INSERT INTO [tb_bill_receivable] (payable_single_id,order_num ,order_date,order_status,order_type,cust_id,cust_code,cust_name,payment_type,payment_money,dealings_balance ,bank_of_deposit,bank_account,org_id,handle,operator,create_by,create_time,status,enable_flag) select @payable_single_id,@order_num,@order_date,@order_status,@order_type,@cust_id,sup_code,sup_short_name,@payment_type,@payment_money,@dealings_balance, null,null,@org_id,@handle,@operator,@create_by,@create_time,@status,@enable_flag from tb_supplier where sup_id =@cust_id"; } list.Add(sql); }
public static void UserLoginOut(string UserID, string currAccDbName) { DateTime dt = DBHelper.GetCurrentTime(currAccDbName); long exitTime = Utility.Common.Common.LocalDateTimeToUtcLong(dt); SysSQLString sysSqlString; List<SysSQLString> list = new List<SysSQLString>(); sysSqlString = new SysSQLString(); sysSqlString.cmdType = CommandType.Text; sysSqlString.sqlString = "UPDATE [sys_user] set [is_online] = '0' where [user_id] = '" + UserID + "'"; list.Add(sysSqlString); sysSqlString = new SysSQLString(); sysSqlString.cmdType = CommandType.Text; sysSqlString.sqlString = "UPDATE [sys_log_log] set exit_time=" + exitTime.ToString() + " where exit_time is null and [user_id] = '" + UserID + "'"; list.Add(sysSqlString); DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("系统登出", currAccDbName, list); }
/// <summary> /// 计算应收应付单据已结算金额 /// </summary> /// <param name="orderType">应收应付单据类型</param> /// <param name="id">应收应付ID</param> /// <param name="list">sql列表</param> public static void DocumentSettlementByBill(DataSources.EnumOrderType orderType, string id, List<SysSQLString> list) { SysSQLString sqlSettlement = new SysSQLString(); sqlSettlement.cmdType = CommandType.StoredProcedure; sqlSettlement.Param = new Dictionary<string, string>(); sqlSettlement.Param.Add("order_id", id); sqlSettlement.Param.Add("type", "1"); if (orderType == DataSources.EnumOrderType.RECEIVABLE) { sqlSettlement.sqlString = "p_yingshou_jiesuan"; } else { sqlSettlement.sqlString = "p_yingfu_jiesuan"; } list.Add(sqlSettlement); }
public void UpdateLastTime(string name, string lastTime) { List<SysSQLString> listSql = new List<SysSQLString>(); SysSQLString sqlUpdate = new SysSQLString(); sqlUpdate.cmdType = CommandType.Text; sqlUpdate.Param = new Dictionary<string, string>(); sqlUpdate.sqlString = string.Format("update sys_config set key_value='{0}' where key_name='{1}'", lastTime, name); listSql.Add(sqlUpdate); try { DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("", GlobalStaticObj_Server.CommAccCode, listSql); } catch (Exception e) { } }
/// <summary> /// 业务打印 /// </summary> /// <param name="dgv">要打印的DataGridView</param> /// <param name="printObject">打印的对象</param> /// <param name="printTitle">打印的标题</param> /// <param name="paperSize">打印页面默认大小,null为A4</param> /// <param name="listNotPrint">不打印的列名</param> public BusinessPrint(DataGridView dgv, string printObject, string printTitle, PaperSize paperSize, List<string> listNotPrint) { this.printObject = printObject; this.printTitle = printTitle; this.paperSize = paperSize; #region 判断是否有当前用户、当前报表的设置,如果没有则创建 if (!DBHelper.IsExist("", "tb_report_set", string.Format("set_object='{0}' and set_user='******'", printObject, GlobalStaticObj.UserID))) { List<SysSQLString> listSql = new List<SysSQLString>(); foreach (DataGridViewColumn dgvc in dgv.Columns) { if (dgvc.Visible && !string.IsNullOrEmpty(dgvc.DataPropertyName)) { SysSQLString sql = new SysSQLString(); sql.cmdType = CommandType.Text; sql.sqlString = @"insert INTO tb_report_set (set_id,set_num,set_object,set_user,set_name,set_data_name,set_width,is_show,is_print,create_time) values (@set_id,@set_num,@set_object,@set_user,@set_name,@set_data_name,@set_width,@is_show,@is_print,@create_time)"; sql.Param = new Dictionary<string, string>(); sql.Param.Add("set_id", Guid.NewGuid().ToString()); sql.Param.Add("set_num", dgvc.Index.ToString()); sql.Param.Add("set_object", printObject); sql.Param.Add("set_user", GlobalStaticObj.UserID); sql.Param.Add("set_name", dgvc.HeaderText); sql.Param.Add("set_data_name", dgvc.DataPropertyName); sql.Param.Add("set_width", dgvc.Width.ToString()); sql.Param.Add("is_show", "1"); if (listNotPrint.Contains(dgvc.Name)) { sql.Param.Add("is_print", "0"); } else { sql.Param.Add("is_print", "1"); } sql.Param.Add("create_time", Common.LocalDateTimeToUtcLong(GlobalStaticObj.CurrentDateTime).ToString()); listSql.Add(sql); } } if (listSql.Count > 0) { DBHelper.BatchExeSQLStringMultiByTrans("", listSql); } } #endregion }
/// <summary> /// 更新最后同步时间 /// </summary> /// <param name="name">名称</param> /// <returns></returns> public void UpdateLastTime(string name) { List<SysSQLString> listSql=new List<SysSQLString> (); SysSQLString sqlUpdate=new SysSQLString (); sqlUpdate.cmdType=CommandType.Text; sqlUpdate.Param=new Dictionary<string,string> (); string lastTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); sqlUpdate.sqlString = string.Format("update sys_config set key_value='{0}',update_time='{1}' where key_name='{2}'", lastTime, Common.LocalDateTimeToUtcLong(GlobalStaticObj_Server.Instance.CurrentDateTime).ToString(), name); listSql.Add(sqlUpdate); try { DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup("更新最后同步时间", GlobalStaticObj_Server.CommAccCode, listSql); } catch (Exception e) { GlobalStaticObj_Server.YTInterfaceLogService.WriteLog("更新最后同步时间", e); } }
internal List<SysSQLString> GetSql(string statusName, DataSources.EnumImportStaus importStaus) { string strImportStatus = ((int)importStaus).ToString(); List<SysSQLString> listSql = new List<SysSQLString>(); if (dicIDs.Count == 0) { return listSql; } foreach (string key in dicIDs.Keys) { SysSQLString sql = new SysSQLString(); sql.cmdType = CommandType.Text; sql.Param = new Dictionary<string, string>(); switch (dicIDs[key].ToString()) { case "销售开单": sql.sqlString = string.Format("update tb_parts_sale_billing set {0}='{1}' where sale_billing_id='{2}';", statusName, strImportStatus, key); break; case "维修结算单": sql.sqlString = string.Format("update tb_maintain_settlement_info set {0}='{1}' where settlement_id='{2}';", statusName, strImportStatus, key); break; case "三包结算单": sql.sqlString = string.Format("update tb_maintain_three_guaranty_settlement set {0}='{1}' where st_id='{2}';", statusName, strImportStatus, key); break; case "销售订单": sql.sqlString = string.Format("update tb_parts_sale_order set {0}='{1}' where sale_order_id='{2}';", statusName, strImportStatus, key); break; case "采购开单": sql.sqlString = string.Format("update tb_parts_purchase_billing set {0}='{1}' where purchase_billing_id='{2}';", statusName, strImportStatus, key); break; case "采购订单": sql.sqlString = string.Format("update tb_parts_purchase_order set {0}='{1}' where order_id='{2}';", statusName, strImportStatus, key); break; } listSql.Add(sql); } return listSql; }
/// <summary> /// 计算往来核销单据已结算金额 /// </summary> /// <param name="enumAccount">往来核销单据类型</param> /// <param name="id">往来核销ID</param> /// <param name="list">sql列表</param> public static void DocumentSettlementByVerification(DataSources.EnumAccountVerification enumAccount, string id, List<SysSQLString> list) { //不为预收转预收,预付转预付的情况,计算已结算金额 if (enumAccount != DataSources.EnumAccountVerification.YuShouToYuShou && enumAccount != DataSources.EnumAccountVerification.YuFuToYuFu) { SysSQLString sqlSettlement = new SysSQLString(); sqlSettlement.cmdType = CommandType.StoredProcedure; sqlSettlement.Param = new Dictionary<string, string>(); sqlSettlement.Param.Add("order_id", id); sqlSettlement.Param.Add("type", "2"); //预收冲应收,应收转应收 if (enumAccount == DataSources.EnumAccountVerification.YuShouToYingShou || enumAccount == DataSources.EnumAccountVerification.YingShouToYingShou) { sqlSettlement.sqlString = "p_yingshou_jiesuan"; } //预付冲应付,应付转应付 else if (enumAccount == DataSources.EnumAccountVerification.YuFuToYingFu || enumAccount == DataSources.EnumAccountVerification.YingFuToYingFu) { sqlSettlement.sqlString = "p_yingfu_jiesuan"; } //应收冲应付,应付冲应收 if (enumAccount == DataSources.EnumAccountVerification.YingShouToYingFu || enumAccount == DataSources.EnumAccountVerification.YingFuToYingShou) { //应收已结算金额 sqlSettlement.sqlString = "p_yingshou_jiesuan"; //应付已结算金额 SysSQLString sqlSettlement1 = new SysSQLString(); sqlSettlement1.cmdType = CommandType.StoredProcedure; sqlSettlement1.Param = new Dictionary<string, string>(); sqlSettlement1.Param.Add("order_id", id); sqlSettlement1.Param.Add("type", "2"); sqlSettlement1.sqlString = "p_yingfu_jiesuan"; list.Add(sqlSettlement1); } list.Add(sqlSettlement); } }
/// <summary> 创建帐套 /// </summary> private void btnCreateAcc_Click(object sender, EventArgs e) { try { List<SysSQLString> list = new List<SysSQLString>(); SysSQLString sysSQLString; StringBuilder strSql; #region 帐套信息 sysSQLString = new SysSQLString(); sysSQLString.cmdType = CommandType.Text; strSql = new StringBuilder(); strSql.Append("insert into sys_setbook("); strSql.Append("id,setbook_name,is_main_set_book,setbook_code,com_name,organization_code,legal_person,opening_bank,bank_account,province,city,county,postal_address,zip_code,company_web_site,email,contact,contact_telephone,status,create_by,create_time,enable_flag"); strSql.Append(") values ("); strSql.Append("@id,@setbook_name,@is_main_set_book,@setbook_code,@com_name,@organization_code,@legal_person,@opening_bank,@bank_account,@province,@city,@county,@postal_address,@zip_code,@company_web_site,@email,@contact,@contact_telephone,@status,@create_by,@create_time,@enable_flag) "); sysSQLString.sqlString = strSql.ToString(); sysSQLString.Param = GetAccParas(); list.Add(sysSQLString); #endregion #region 维修参数 sysSQLString = new SysSQLString(); sysSQLString.cmdType = CommandType.Text; strSql = new StringBuilder(); strSql.Append("insert into sys_repair_param("); strSql.Append("r_param_id,appointment_audit,r_reception_audit,r_schedul_quality_ins,r_settlement_audit,rescue_audit,r_return_audit,requisition_audit,material_return_audit,repail_flow1,single_editors_same_person,single_audit_same_person,single_disabled_same_person,single_delete_same_person,repair_reception_import_pre,repair_return_import_pre,requisition_import_pre,material_return_import_pre,three_service_import_pre_yt,old_pieces_storage_import_pre,allow_material_larger_parts_r,requisition_auto_outbound,create_by,create_time,book_id,repail_flow2,repail_flow3,time_standards"); strSql.Append(") values ("); strSql.Append("@r_param_id,@appointment_audit,@r_reception_audit,@r_schedul_quality_ins,@r_settlement_audit,@rescue_audit,@r_return_audit,@requisition_audit,@material_return_audit,@repail_flow1,@single_editors_same_person,@single_audit_same_person,@single_disabled_same_person,@single_delete_same_person,@repair_reception_import_pre,@repair_return_import_pre,@requisition_import_pre,@material_return_import_pre,@three_service_import_pre_yt,@old_pieces_storage_import_pre,@allow_material_larger_parts_r,@requisition_auto_outbound,@create_by,@create_time,@book_id,@repail_flow2,@repail_flow3,@time_standards) "); sysSQLString.sqlString = strSql.ToString(); sysSQLString.Param = GetRepairParas(); list.Add(sysSQLString); #endregion #region 采购参数 sysSQLString = new SysSQLString(); sysSQLString.cmdType = CommandType.Text; strSql = new StringBuilder(); strSql.Append("insert into sys_purchase_param("); strSql.Append("purchase_param_id,purchase_plan_audit,purchase_order_audit,purchase_open_audit,purchase_order_audit_yt,purchase_open_outin,single_editors_same_person,single_audit_same_person,single_disabled_same_person,single_delete_same_person,purchase_order_import_pre,purchase_open_import_pre,create_by,create_time,book_id"); strSql.Append(") values ("); strSql.Append("@purchase_param_id,@purchase_plan_audit,@purchase_order_audit,@purchase_open_audit,@purchase_order_audit_yt,@purchase_open_outin,@single_editors_same_person,@single_audit_same_person,@single_disabled_same_person,@single_delete_same_person,@purchase_order_import_pre,@purchase_open_import_pre,@create_by,@create_time,@book_id) "); sysSQLString.sqlString = strSql.ToString(); sysSQLString.Param = GetPurchaseParas(); list.Add(sysSQLString); #endregion #region 销售参数 sysSQLString = new SysSQLString(); sysSQLString.cmdType = CommandType.Text; strSql = new StringBuilder(); strSql.Append("insert into sys_sale_param("); strSql.Append("sale_param_id,sales_plan_audit,sales_order_audit,sales_open_audit,sales_open_outin,sales_order_line_credit,sales_open_line_credit,single_editors_same_person,single_audit_same_person,single_disabled_same_person,single_delete_same_person,create_by,create_time,book_id"); strSql.Append(") values ("); strSql.Append("@sale_param_id,@sales_plan_audit,@sales_order_audit,@sales_open_audit,@sales_open_outin,@sales_order_line_credit,@sales_open_line_credit,@single_editors_same_person,@single_audit_same_person,@single_disabled_same_person,@single_delete_same_person,@create_by,@create_time,@book_id) "); sysSQLString.sqlString = strSql.ToString(); sysSQLString.Param = GetSaleParas(); list.Add(sysSQLString); #endregion #region 库存参数 sysSQLString = new SysSQLString(); sysSQLString.cmdType = CommandType.Text; strSql = new StringBuilder(); strSql.Append("insert into sys_stock_param("); strSql.Append("stock_param_id,storage_manage,making_audit_one_person,allow_zero_lib_stock,allow_zero_lib_junction,single_editors_one_person,single_audit_one_person,single_disabled_one_person,single_delete_one_person,monthly_average_method,moving_average_method,fifo_method,counts,counts_zero,price,price_zero,warehous_single_reference,create_by,create_time,book_id,batch_manage"); strSql.Append(") values ("); strSql.Append("@stock_param_id,@storage_manage,@making_audit_one_person,@allow_zero_lib_stock,@allow_zero_lib_junction,@single_editors_one_person,@single_audit_one_person,@single_disabled_one_person,@single_delete_one_person,@monthly_average_method,@moving_average_method,@fifo_method,@counts,@counts_zero,@price,@price_zero,@warehous_single_reference,@create_by,@create_time,@book_id,@batch_manage) "); sysSQLString.sqlString = strSql.ToString(); sysSQLString.Param = GetStockParas(); list.Add(sysSQLString); #endregion #region 财务参数 sysSQLString = new SysSQLString(); sysSQLString.cmdType = CommandType.Text; strSql = new StringBuilder(); strSql.Append("insert into sys_financial_ser_param("); strSql.Append("financial_ser_param_id,tax_rate,currency,counts,counts_zero,price,price_zero,create_by,create_time,book_id"); strSql.Append(") values ("); strSql.Append("@financial_ser_param_id,@tax_rate,@currency,@counts,@counts_zero,@price,@price_zero,@create_by,@create_time,@book_id) "); sysSQLString.sqlString = strSql.ToString(); sysSQLString.Param = GetFinancParas(); list.Add(sysSQLString); #endregion #region 创建帐套 try { string newDbName = GlobalStaticObj_Server.DbPrefix + txtsetbook_code.Caption.Trim(); Dictionary<string, string> dicParam = new Dictionary<string, string>(); dicParam.Add("newDbName", newDbName);//新建数据库名(不带扩展名) dicParam.Add("dbDataDirPath", GlobalStaticObj_Server.Instance.DbServerInstallDir);//服务端数据库安装路径(带\) dicParam.Add("soureDbName", GlobalStaticObj_Server.Instance.DbTemplateBakFileName);//原数据库名(不带扩展名) dicParam.Add("soureBackupFilePATH", GlobalStaticObj_Server.Instance.DbServerBackDir + GlobalStaticObj_Server.Instance.DbTemplateBakFileName + ".bak");//服务端备份文件路径(带文件名扩展名) DBHelper.ExtNonQuery("创建帐套", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.CommAccCode, "sp_createdbbybak", CommandType.StoredProcedure, dicParam); } catch (Exception ex) { MessageBoxEx.Show("帐套创建失败" + ex.Message, "系统提示"); OpType = 0; return; } #endregion bool flag = DBHelper.BatchExeSQLStringMultiByTrans("创建帐套", GlobalStaticObj_Server.DbPrefix + GlobalStaticObj_Server.CommAccCode, list); if (!flag) { MessageBoxEx.Show("帐套创建失败", "系统提示"); return; } MessageBoxEx.Show("帐套创建成功", "系统提示"); this.DialogResult = DialogResult.OK; } catch (Exception ex) { GlobalStaticObj_Server.GlobalLogService.WriteLog("帐套设置", ex); MessageBoxEx.ShowWarning("程序异常"); } }
/// <summary> 添加情况下组装sql的方法 /// </summary> /// <param name="listSql"></param> /// <param name="purchase_order_yt_id"></param> private void AddPurchaseOrderSqlString(List<SysSQLString> listSql, string purchase_order_yt_id, string HandleType) { SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数 decimal application_count = 0; decimal conf_count = 0; string parts_codes=string.Empty; string parts_names = string.Empty; GetBuesinessCountPartsInfo(ref application_count, ref conf_count, ref parts_codes, ref parts_names); ddtorder_date.Value = Convert.ToDateTime(ddtorder_date.Value.ToShortDateString() + " 23:59:59"); tb_parts_purchase_order_2 model = new tb_parts_purchase_order_2(); CommonFuncCall.SetModelObjectValue(this, model); if (model.crm_bill_id == ".") { model.crm_bill_id = string.Empty; } model.order_type_name = ddlorder_type.SelectedItem.ToString(); if (ddlorder_type.SelectedValue.ToString() == "order_type_100000001")//配件需求订单 { xuqiu.GetControlInfo(model); } else if (ddlorder_type.SelectedValue.ToString() == "order_type_100000005")//产品升级订单 { shengji.GetControlInfo(model); } else if (ddlorder_type.SelectedValue.ToString() == "order_type_100000004")//新三包调件订单 { sanbao.GetControlInfo(model); } model.application_count = application_count; model.conf_count = conf_count; model.purchase_order_yt_id = purchase_order_yt_id; model.create_by = GlobalStaticObj.UserID; model.create_name = GlobalStaticObj.UserName; model.create_time = Common.LocalDateTimeToUtcLong(DateTime.Now); model.operators = GlobalStaticObj.UserID; model.operator_name = GlobalStaticObj.UserName; model.com_id = GlobalStaticObj.CurrUserCom_Id;//公司ID model.com_code = GlobalStaticObj.CurrUserCom_Code;//公司编码 model.com_name = GlobalStaticObj.CurrUserCom_Name;//公司名称 if (!string.IsNullOrEmpty(ddlorg_id.SelectedValue.ToString())) { model.org_id = ddlorg_id.SelectedValue.ToString(); model.org_name = ddlorg_id.SelectedItem.ToString(); } if (!string.IsNullOrEmpty(ddlhandle.SelectedValue.ToString())) { model.handle = ddlhandle.SelectedValue.ToString(); model.handle_name = ddlhandle.SelectedItem.ToString(); } model.parts_codes = parts_codes; model.parts_names = parts_names; model.enable_flag = "1"; if (HandleType == "保存") { model.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.DRAFT).ToString(); model.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.DRAFT, true); } else if (HandleType == "提交") { model.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.SUBMIT).ToString(); model.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.SUBMIT, true); } if (model != null) { StringBuilder sb = new StringBuilder(); sb.Append(" Insert Into tb_parts_purchase_order_2( "); StringBuilder sp = new StringBuilder(); StringBuilder sb_prame = new StringBuilder(); foreach (PropertyInfo info in model.GetType().GetProperties()) { string name = info.Name; //外部加入的属性 if (name == "listDetails") { break; } object value = info.GetValue(model, null); sb_prame.Append("," + name); sp.Append(",@" + name); dicParam.Add(name, value == null ? "" : value.ToString()); } sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values ("); sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")").Append(";"); sysStringSql.sqlString = sb.ToString(); sysStringSql.Param = dicParam; listSql.Add(sysStringSql); } }
/// <summary> 在编辑和添加时对主数据和采购订单配件表中的配件信息进行操作 /// </summary> /// <param name="listSql"></param> /// <param name="purchase_order_yt_id"></param> private void DealAccessories(List<SysSQLString> listSql, string purchase_order_yt_id) { SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; Dictionary<string, string> dic = new Dictionary<string, string>(); string sql1 = "delete from tb_parts_purchase_order_p_2 where purchase_order_yt_id=@purchase_order_yt_id;"; dic.Add("purchase_order_yt_id", purchase_order_yt_id); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); if (gvPurchaseList.Rows.Count > 0) { foreach (DataGridViewRow dr in gvPurchaseList.Rows) { if (dr.Cells["parts_code"].Value == null) { continue; } sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); dic.Add("id", Guid.NewGuid().ToString()); dic.Add("purchase_order_yt_id", purchase_order_yt_id); dic.Add("parts_id", dr.Cells["parts_id"].Value == null ? "" : dr.Cells["parts_id"].Value.ToString()); dic.Add("parts_code", dr.Cells["parts_code"].Value == null ? "" : dr.Cells["parts_code"].Value.ToString()); dic.Add("parts_name", dr.Cells["parts_name"].Value == null ? "" : dr.Cells["parts_name"].Value.ToString()); dic.Add("car_factory_code", dr.Cells["car_factory_code"].Value == null ? "" : dr.Cells["car_factory_code"].Value.ToString()); dic.Add("drawing_num", dr.Cells["drawing_num"].Value == null ? "" : dr.Cells["drawing_num"].Value.ToString()); dic.Add("model", dr.Cells["model"].Value == null ? "" : dr.Cells["model"].Value.ToString()); dic.Add("application_count", dr.Cells["application_count"].Value == null ? "0" : dr.Cells["application_count"].Value.ToString() == "" ? "0" : dr.Cells["application_count"].Value.ToString()); dic.Add("price", dr.Cells["price"].Value == null ? "0" : dr.Cells["price"].Value.ToString() == "" ? "0" : dr.Cells["price"].Value.ToString()); dic.Add("money", dr.Cells["money"].Value == null ? "0" : dr.Cells["money"].Value.ToString() == "" ? "0" : dr.Cells["money"].Value.ToString()); dic.Add("conf_count", dr.Cells["conf_count"].Value == null ? "0" : dr.Cells["conf_count"].Value.ToString() == "" ? "0" : dr.Cells["conf_count"].Value.ToString()); dic.Add("parts_explain", dr.Cells["parts_explain"].Value == null ? "" : dr.Cells["parts_explain"].Value.ToString()); dic.Add("replaces", dr.Cells["replaces"].Value == null ? "" : dr.Cells["replaces"].Value.ToString()); if (dr.Cells["unit_id"].Value == null) { dic.Add("unit_id", string.Empty); dic.Add("unit_name", string.Empty); } else { dic.Add("unit_id", dr.Cells["unit_id"].Value.ToString()); dic.Add("unit_name", dr.Cells["unit_id"].EditedFormattedValue.ToString()); } dic.Add("center_library_explain", dr.Cells["center_library_explain"].Value == null ? "" : dr.Cells["center_library_explain"].Value.ToString()); //dic.Add("total_library_explain", dr.Cells["total_library_explain"].Value == null ? "" : dr.Cells["total_library_explain"].Value.ToString()); dic.Add("cancel_reasons", dr.Cells["cancel_reasons"].Value == null ? "" : dr.Cells["cancel_reasons"].Value.ToString()); dic.Add("relation_order", dr.Cells["relation_order"].Value == null ? "" : dr.Cells["relation_order"].Value.ToString()); dic.Add("create_by", dr.Cells["create_by"].Value == null ? "" : dr.Cells["create_by"].Value.ToString()); dic.Add("create_name", dr.Cells["create_name"].Value == null ? "" : dr.Cells["create_name"].Value.ToString()); dic.Add("create_time", Common.LocalDateTimeToUtcLong(Convert.ToDateTime(dr.Cells["create_time"].Value == null ? DateTime.Now.ToString() : dr.Cells["create_time"].Value.ToString())).ToString()); dic.Add("update_by", GlobalStaticObj.UserID); dic.Add("update_name", GlobalStaticObj.UserName); dic.Add("update_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString()); dic.Add("ImportType", dr.Cells["ImportType"].Value == null ? "" : dr.Cells["ImportType"].Value.ToString()); string sql2 = string.Format(@"Insert Into tb_parts_purchase_order_p_2(id,purchase_order_yt_id,parts_id,parts_code,parts_name,car_factory_code, drawing_num,model,application_count,price,money,conf_count,parts_explain,replaces, unit_id,unit_name,center_library_explain,cancel_reasons,relation_order,create_by,create_name,create_time, update_by,update_name,update_time,ImportType) values(@id,@purchase_order_yt_id,@parts_id,@parts_code,@parts_name,@car_factory_code, @drawing_num,@model,@application_count,@price,@money,@conf_count,@parts_explain,@replaces, @unit_id,@unit_name,@center_library_explain,@cancel_reasons,@relation_order,@create_by,@create_name,@create_time, @update_by,@update_name,@update_time,@ImportType);"); sysStringSql.sqlString = sql2; sysStringSql.Param = dic; listSql.Add(sysStringSql); } } }
/// <summary> 对引用的前置单据的状态进行更新的方法 /// </summary> /// <param name="list_order"></param> void ImportPurchasePlanStatus(List<OrderImportStatus> list_order, List<OrderFinishInfo> list_orderinfo) { SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; List<SysSQLString> listSql = new List<SysSQLString>(); Dictionary<string, string> dic = new Dictionary<string, string>(); List<string> list_plan = new List<string>(); string plan_ids = string.Empty; #region 更新前置单据的导入状态字段 foreach (OrderImportStatus item in list_order) { if (item.importtype == "采购计划单") { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); string sql1 = "update tb_parts_purchase_plan set import_status=@import_status where order_num=@order_num;"; dic.Add("import_status", !item.isfinish ? "2" : "3");//单据导入状态,0正常,1占用,2锁定(部分导入), 3锁定(全部导入) dic.Add("order_num", item.order_num); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); } else if (item.importtype == "销售订单") { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); string sql1 = "update tb_parts_sale_order set is_occupy=@is_occupy where order_num=@order_num;"; dic.Add("is_occupy", !item.isfinish ? "2" : "3");//单据导入状态,0正常,1占用,2锁定(部分导入), 3锁定(全部导入) dic.Add("order_num", item.order_num); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); } } #endregion #region 更新前置单据中的各个配件的已完成数量 foreach (OrderFinishInfo item in list_orderinfo) { if (!list_plan.Contains(item.plan_id)) { list_plan.Add(item.plan_id); plan_ids = plan_ids + "'" + item.plan_id + "',"; } if (item.importtype == "采购计划单") { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); string sql1 = "update tb_parts_purchase_plan_p set finish_counts=@finish_counts where plan_id=@plan_id and parts_code=@parts_code;"; dic.Add("finish_counts", item.finish_num); dic.Add("plan_id", item.plan_id); dic.Add("parts_code", item.parts_code); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); } else if (item.importtype == "销售订单") { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); string sql1 = "update tb_parts_sale_order_p set finish_count=@finish_count where sale_order_id=@sale_order_id and parts_code=@parts_code;"; dic.Add("finish_count", item.finish_num); dic.Add("sale_order_id", item.plan_id); dic.Add("parts_code", item.parts_code); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); } } #endregion bool ret = DBHelper.BatchExeSQLStringMultiByTrans("提交宇通采购订单,更新引用的采购计划单或销售订单的导入状态", listSql); if (ret) { if (list_orderinfo.Count > 0) { listSql.Clear(); plan_ids = plan_ids.Trim(','); string TableName = string.Format(@" ( select plan_id,sum(finish_counts) finish_counts, sum(finish_counts*business_price) as finish_money from tb_parts_purchase_plan_p where plan_id in ({0}) group by plan_id ) tb_purchase_finish", plan_ids); DataTable dt = DBHelper.GetTable("查询采购计划单各配件完成数量和完成金额", TableName, "*", "", "", ""); if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); string sql1 = "update tb_parts_purchase_plan set finish_counts=@finish_counts,plan_finish_money=@plan_finish_money where plan_id=@plan_id;"; dic.Add("finish_counts", dt.Rows[i]["finish_counts"].ToString()); dic.Add("plan_finish_money", dt.Rows[i]["finish_money"].ToString()); dic.Add("plan_id", dt.Rows[i]["plan_id"].ToString()); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); } DBHelper.BatchExeSQLStringMultiByTrans("完成宇通采购订单后,更新采购计划单的完成数量和完成金额", listSql); } } } }
/// <summary> 提交时获取当前配件列表中存在的引用单号,保存到中间表中 /// 并生成执行的sql /// </summary> /// <returns></returns> void GetPre_Order_Code(List<SysSQLString> listSql, string post_order_id, string post_order_code) { List<string> list = new List<string>(); SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; Dictionary<string, string> dic = new Dictionary<string, string>(); string sql1 = "delete from tr_order_relation where post_order_id=@post_order_id and post_order_code=@post_order_code;"; dic.Add("post_order_id", post_order_id); dic.Add("post_order_code", post_order_code); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); foreach (DataGridViewRow dr in gvPurchaseList.Rows) { if (dr.Cells["parts_code"].Value == null) { continue; } string relation_order = dr.Cells["relation_order"].Value == null ? "" : dr.Cells["relation_order"].Value.ToString(); if (!string.IsNullOrEmpty(relation_order)) { if (!list.Contains(relation_order)) { list.Add(relation_order); sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); dic.Add("order_relation_id", Guid.NewGuid().ToString()); dic.Add("pre_order_id", string.Empty); dic.Add("pre_order_code", relation_order); dic.Add("post_order_id", post_order_id); dic.Add("post_order_code", post_order_code); string sql2 = string.Format(@"Insert Into tr_order_relation(order_relation_id,pre_order_id,pre_order_code, post_order_id,post_order_code) values(@order_relation_id,@pre_order_id, @pre_order_code,@post_order_id,@post_order_code);"); sysStringSql.sqlString = sql2; sysStringSql.Param = dic; listSql.Add(sysStringSql); } } } }
/// <summary> /// 在编辑和添加时对主数据和仓库货位表中的信息进行操作 /// </summary> /// <param name="listSql"></param> /// <param name="supID"></param> private void DealCargospace(List<SysSQLString> listSql, string wh_id) { dgvWareHouseList.EndEdit(); SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; Dictionary<string, string> dic = new Dictionary<string, string>(); if (dgvWareHouseList.Rows.Count > 0) { string sql1 = "delete from tb_cargo_space where wh_id=@wh_id;"; dic.Add("wh_id", wh_id); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); foreach (DataGridViewRow dr in dgvWareHouseList.Rows) { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); dic.Add("cs_id", Guid.NewGuid().ToString()); dic.Add("wh_id", wh_id); dic.Add("cs_code", dr.Cells["colcs_code"].Value == null ? "" : dr.Cells["colcs_code"].Value.ToString()); dic.Add("cs_name", dr.Cells["colcs_name"].Value == null ? "" : dr.Cells["colcs_name"].Value.ToString()); dic.Add("stock_u_limit", dr.Cells["colstock_u_limit"].Value == null ? "" : dr.Cells["colstock_u_limit"].Value.ToString()); dic.Add("stock_l_limit", dr.Cells["colstock_l_limit"].Value == null ? "" : dr.Cells["colstock_l_limit"].Value.ToString()); dic.Add("remark", dr.Cells["colremark"].Value == null ? "" : dr.Cells["colremark"].Value.ToString()); dic.Add("status", SYSModel.DataSources.EnumStatus.Start.ToString()); dic.Add("enable_flag", "1"); dic.Add("create_by", GlobalStaticObj.UserID); dic.Add("create_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString()); string sql2 = "Insert Into tb_cargo_space(cs_id,wh_id,cs_code,cs_name,stock_u_limit,stock_l_limit,remark,status,enable_flag,create_by,create_time) " + " values(@cs_id,@wh_id,@cs_code,@cs_name,@stock_u_limit,@stock_l_limit,@remark,@status,@enable_flag,@create_by,@create_time);"; sysStringSql.sqlString = sql2; sysStringSql.Param = dic; listSql.Add(sysStringSql); } } }
/// <summary> /// 编辑供应商档案的sql语句 /// </summary> /// <param name="listSql"></param> /// <param name="supID"></param> /// <param name="model"></param> private void EditWareHouseSqlString(List<SysSQLString> listSql, string whId, tb_warehouse model) { SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数 CommonFuncCall.SetModelObjectValue(this, model); model.update_by = GlobalStaticObj.UserID; model.update_time = Common.LocalDateTimeToUtcLong(DateTime.Now); model.com_id = txtChooseCompanty.Tag == null ? "" : txtChooseCompanty.Tag.ToString(); model.wh_head = txtChooseWhHead.Text; if (model != null) { StringBuilder sb = new StringBuilder(); sb.Append(" Update tb_warehouse Set "); bool isFirstValue = true; foreach (PropertyInfo info in model.GetType().GetProperties()) { string name = info.Name; object value = info.GetValue(model, null); if (isFirstValue) { isFirstValue = false; sb.Append(name); sb.Append("="); sb.Append("@" + name); } else { sb.Append("," + name); sb.Append("="); sb.Append("@" + name); } dicParam.Add(name, value == null ? "" : value.ToString()); } sb.Append(" where wh_id='" + whId + "';"); sysStringSql.sqlString = sb.ToString(); sysStringSql.Param = dicParam; listSql.Add(sysStringSql); } }
/// <summary> 提交事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> void UCPurchaseBillManang_SubmitEvent(object sender, EventArgs e) { try { List<string> listField = GetSelectedRecord(); if (listField.Count == 0) { MessageBoxEx.Show("请选择要提交的数据!"); return; } if (MessageBoxEx.Show("确认要提交选中的数据吗?", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Question) != DialogResult.OK) { return; } foreach (DataGridViewRow dr in gvPurchaseOrderList.Rows) { object isCheck = dr.Cells["colCheck"].EditedFormattedValue; if (isCheck != null && (bool)isCheck) { SysSQLString obj = new SysSQLString(); obj.cmdType = CommandType.Text; List<SysSQLString> listSql = new List<SysSQLString>(); Dictionary<string, string> dicParam = new Dictionary<string, string>(); string order_num = string.Empty; if (dr.Cells["order_status"].Value.ToString() == Convert.ToInt32(DataSources.EnumAuditStatus.DRAFT).ToString())//草稿状态 { if (dr.Cells["order_num"].Value != null && dr.Cells["order_num"].Value.ToString().Length > 0) { order_num = dr.Cells["order_num"].Value.ToString(); } else { order_num = CommonUtility.GetNewNo(DataSources.EnumProjectType.PurchaseOpenOrder); } } else if (dr.Cells["order_status"].Value.ToString() == Convert.ToInt32(DataSources.EnumAuditStatus.NOTAUDIT).ToString())//审核未通过 { order_num = dr.Cells["order_num"].Value.ToString(); } dicParam.Add("purchase_billing_id", dr.Cells["purchase_billing_id"].Value.ToString());//单据ID dicParam.Add("order_num", order_num);//单据编号 dicParam.Add("order_status", Convert.ToInt32(DataSources.EnumAuditStatus.SUBMIT).ToString());//单据状态ID dicParam.Add("order_status_name", DataSources.GetDescription(DataSources.EnumAuditStatus.SUBMIT, true));//单据状态名称 dicParam.Add("update_by", GlobalStaticObj.UserID);//修改人ID dicParam.Add("update_name", GlobalStaticObj.UserName);//修改人姓名 dicParam.Add("update_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString());//修改时间 obj.sqlString = "update tb_parts_purchase_billing set order_num=@order_num,order_status=@order_status,order_status_name=@order_status_name,update_by=@update_by,update_name=@update_name,update_time=@update_time where purchase_billing_id=@purchase_billing_id"; obj.Param = dicParam; listSql.Add(obj); if (GetIsSubmit(dr.Cells["purchase_billing_id"].Value.ToString(), dr.Cells["order_type"].Value.ToString())) { GetPre_Order_Code(listSql, dr.Cells["purchase_billing_id"].Value.ToString(), dr.Cells["purchase_billing_id"].Value.ToString(), order_num); if (DBHelper.BatchExeSQLStringMultiByTrans("更新单据状态为提交", listSql)) { SetOrderStatus(dr.Cells["purchase_billing_id"].Value.ToString()); } } else { BindgvPurchaseOrderList(); return; } } } MessageBoxEx.Show("提交单据完成!"); BindgvPurchaseOrderList(); } catch (Exception ex) { } }
/// <summary> /// 执行启用停用 /// </summary> /// <returns>是否成功</returns> private bool StatusSql() { List<SysSQLString> listSql = new List<SysSQLString>(); Dictionary<string, string> dicStatus = new Dictionary<string, string>(); SysSQLString sql = new SysSQLString(); sql.cmdType = CommandType.Text; sql.Param = new Dictionary<string, string>(); string strSql = "update tb_organization set status=@status where org_id in ({0})"; string ids = string.Empty; if (enumStatus == DataSources.EnumStatus.Start) { sql.Param.Add("status", ((int)DataSources.EnumStatus.Stop).ToString()); foreach (string id in listStart) { ids += string.Format("'{0}',", id); } ids = ids.TrimEnd(','); } else if (enumStatus == DataSources.EnumStatus.Stop) { sql.Param.Add("status", ((int)DataSources.EnumStatus.Start).ToString()); foreach (string id in listStop) { ids += string.Format("'{0}',", id); } ids = ids.TrimEnd(','); } sql.sqlString = string.Format(strSql, ids); listSql.Add(sql); return DBHelper.BatchExeSQLStrMultiByTransNoLogNoBackup(btnStatus.Caption + "银行账户", listSql); }
/// <summary> /// 在编辑和添加时对主数据和销售开单配件表中的配件信息进行操作 /// </summary> /// <param name="listSql"></param> /// <param name="sale_billing_id"></param> private void DealAccessories(List<SysSQLString> listSql, string sale_billing_id) { string PartsCodes = GetListPartsCodes(); DataTable dt_CarType = CommonFuncCall.GetCarType(PartsCodes); DataTable dt_PartsType = CommonFuncCall.GetPartsType(PartsCodes); SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; Dictionary<string, string> dic = new Dictionary<string, string>(); string sql1 = "delete from tb_parts_sale_billing_p where sale_billing_id=@sale_billing_id;"; dic.Add("sale_billing_id", sale_billing_id); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); if (gvPurchaseList.Rows.Count > 0) { foreach (DataGridViewRow dr in gvPurchaseList.Rows) { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); dic.Add("sale_billing_parts_id", Guid.NewGuid().ToString()); dic.Add("sale_billing_id", sale_billing_id); if (dr.Cells["wh_id"].Value == null) { dic.Add("wh_id", string.Empty); dic.Add("wh_name", string.Empty); } else { dic.Add("wh_id", dr.Cells["wh_id"].Value.ToString()); dic.Add("wh_name", dr.Cells["wh_id"].EditedFormattedValue.ToString()); } string parts_code = string.Empty; if (dr.Cells["parts_code"].Value == null) { parts_code = string.Empty; } else { parts_code = dr.Cells["parts_code"].Value.ToString(); } dic.Add("parts_code", parts_code); dic.Add("parts_name", dr.Cells["parts_name"].Value == null ? "" : dr.Cells["parts_name"].Value.ToString()); dic.Add("model", dr.Cells["model"].Value == null ? "" : dr.Cells["model"].Value.ToString()); dic.Add("drawing_num", dr.Cells["drawing_num"].Value == null ? "" : dr.Cells["drawing_num"].Value.ToString()); if (dr.Cells["unit_id"].Value == null) { dic.Add("unit_id", string.Empty); dic.Add("unit_name", string.Empty); } else { dic.Add("unit_id", dr.Cells["unit_id"].Value.ToString()); dic.Add("unit_name", dr.Cells["unit_id"].EditedFormattedValue.ToString()); } dic.Add("parts_brand", dr.Cells["parts_brand"].Value == null ? "" : dr.Cells["parts_brand"].Value.ToString()); dic.Add("parts_brand_name", dr.Cells["parts_brand_name"].Value == null ? "" : dr.Cells["parts_brand_name"].Value.ToString()); dic.Add("business_count", dr.Cells["business_count"].Value == null ? "0" : dr.Cells["business_count"].Value.ToString() == "" ? "0" : dr.Cells["business_count"].Value.ToString()); dic.Add("original_price", dr.Cells["original_price"].Value == null ? "0" : dr.Cells["original_price"].Value.ToString() == "" ? "0" : dr.Cells["original_price"].Value.ToString()); dic.Add("discount", dr.Cells["discount"].Value == null ? "0" : dr.Cells["discount"].Value.ToString() == "" ? "0" : dr.Cells["discount"].Value.ToString()); dic.Add("business_price", dr.Cells["business_price"].Value == null ? "0" : dr.Cells["business_price"].Value.ToString() == "" ? "0" : dr.Cells["business_price"].Value.ToString()); dic.Add("tax_rate", dr.Cells["tax_rate"].Value == null ? "0" : dr.Cells["tax_rate"].Value.ToString() == "" ? "0" : dr.Cells["tax_rate"].Value.ToString()); dic.Add("tax", dr.Cells["tax"].Value == null ? "0" : dr.Cells["tax"].Value.ToString() == "" ? "0" : dr.Cells["tax"].Value.ToString()); dic.Add("payment", dr.Cells["payment"].Value == null ? "0" : dr.Cells["payment"].Value.ToString() == "" ? "0" : dr.Cells["payment"].Value.ToString()); dic.Add("valorem_together", dr.Cells["valorem_together"].Value == null ? "0" : dr.Cells["valorem_together"].Value.ToString() == "" ? "0" : dr.Cells["valorem_together"].Value.ToString());//价税合计 dic.Add("auxiliary_count", dr.Cells["auxiliary_count"].Value == null ? "0" : dr.Cells["auxiliary_count"].Value.ToString() == "" ? "0" : dr.Cells["auxiliary_count"].Value.ToString());//辅助数量 dic.Add("relation_order", dr.Cells["relation_order"].Value == null ? "" : dr.Cells["relation_order"].Value.ToString());//引用单号 dic.Add("is_gift", dr.Cells["is_gift"].Value == null ? "1" : dr.Cells["is_gift"].Value.ToString()); //if (dr.Cells["make_date"].Value != null) //{ // dic.Add("make_date", Common.LocalDateTimeToUtcLong(Convert.ToDateTime(dr.Cells["make_date"].Value.ToString())).ToString());//生产日期 //} //else //{ // dic.Add("make_date", "0");//生产日期 //} //if (dr.Cells["arrival_date"].Value != null) //{ // dic.Add("arrival_date", Common.LocalDateTimeToUtcLong(Convert.ToDateTime(dr.Cells["arrival_date"].Value.ToString())).ToString());//到期日期 //} //else //{ // dic.Add("arrival_date", "0");//到期日期 //} dic.Add("remark", dr.Cells["remark"].Value == null ? "" : dr.Cells["remark"].Value.ToString()); dic.Add("return_count", dr.Cells["return_count"].Value == null ? "0" : dr.Cells["return_count"].Value.ToString() == "" ? "0" : dr.Cells["return_count"].Value.ToString());//退货业务数量 dic.Add("library_count", dr.Cells["library_count"].Value == null ? "0" : dr.Cells["library_count"].Value.ToString() == "" ? "0" : dr.Cells["library_count"].Value.ToString());//入库数量 dic.Add("car_factory_code", dr.Cells["car_factory_code"].Value == null ? "" : dr.Cells["car_factory_code"].Value.ToString());//车厂配件编码 dic.Add("create_by", dr.Cells["create_by"].Value == null ? "" : dr.Cells["create_by"].Value.ToString()); dic.Add("create_name", dr.Cells["create_name"].Value == null ? "" : dr.Cells["create_name"].Value.ToString()); dic.Add("create_time", Common.LocalDateTimeToUtcLong(Convert.ToDateTime(dr.Cells["create_time"].Value == null ? DateTime.Now.ToString() : dr.Cells["create_time"].Value.ToString())).ToString()); dic.Add("update_by", GlobalStaticObj.UserID); dic.Add("update_name", GlobalStaticObj.UserName); dic.Add("update_time", Common.LocalDateTimeToUtcLong(DateTime.Now).ToString()); dic.Add("ImportOrderType", dr.Cells["ImportOrderType"].Value == null ? "" : dr.Cells["ImportOrderType"].Value.ToString()); dic.Add("finish_count", "0"); string vm_name = string.Empty; if (dt_CarType != null && dt_CarType.Rows.Count > 0) { if (!string.IsNullOrEmpty(parts_code)) { DataRow[] dr_cartype = dt_CarType.Select(" ser_parts_code='" + parts_code + "'"); if (dr_cartype.Length > 0) { foreach (DataRow item in dr_cartype) { vm_name += "" + item["vm_name"].ToString() + ","; } } } } vm_name = vm_name.Trim(','); string parts_type_id = string.Empty; string parts_type_name = string.Empty; if (dt_PartsType != null && dt_PartsType.Rows.Count > 0) { if (!string.IsNullOrEmpty(parts_code)) { DataRow[] dr_partstype = dt_PartsType.Select(" ser_parts_code='" + parts_code + "'"); if (dr_partstype.Length > 0) { parts_type_id = dr_partstype[0]["dic_id"].ToString(); parts_type_name = dr_partstype[0]["dic_name"].ToString(); } } } dic.Add("vm_name", vm_name); dic.Add("parts_type_id", parts_type_id); dic.Add("parts_type_name", parts_type_name); string sql2 = string.Format(@"Insert Into tb_parts_sale_billing_p(sale_billing_parts_id,sale_billing_id,wh_id,wh_name, parts_code,parts_name,model,drawing_num,unit_id,unit_name,parts_brand,parts_brand_name,business_count,original_price,discount,business_price, tax_rate,tax,payment,valorem_together,auxiliary_count,relation_order,is_gift,remark, return_count,library_count,car_factory_code,create_by,create_name,create_time,update_by,update_name, update_time,ImportOrderType,finish_count,vm_name,parts_type_id,parts_type_name) values(@sale_billing_parts_id,@sale_billing_id,@wh_id,@wh_name, @parts_code,@parts_name,@model,@drawing_num,@unit_id,@unit_name,@parts_brand,@parts_brand_name,@business_count,@original_price,@discount,@business_price, @tax_rate,@tax,@payment,@valorem_together,@auxiliary_count,@relation_order,@is_gift,@remark, @return_count,@library_count,@car_factory_code,@create_by,@create_name,@create_time,@update_by,@update_name,@update_time, @ImportOrderType,@finish_count,@vm_name,@parts_type_id,@parts_type_name);"); sysStringSql.sqlString = sql2; sysStringSql.Param = dic; listSql.Add(sysStringSql); } } }
/// <summary> 对引用的前置单据的状态进行更新的方法 /// </summary> /// <param name="list_order"></param> void ImportPurchasePlanStatus(List<OrderImportStatus> list_order, List<OrderFinishInfo> list_orderinfo) { SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; List<SysSQLString> listSql = new List<SysSQLString>(); Dictionary<string, string> dic = new Dictionary<string, string>(); //更新前置单据的导入状态字段 foreach (OrderImportStatus item in list_order) { if (item.importtype == "销售订单") { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); string sql1 = "update tb_parts_sale_order set is_occupy=@is_occupy where order_num=@order_num;"; dic.Add("is_occupy", !item.isfinish ? "2" : "3");//单据导入状态,0正常,1占用,2锁定(部分导入), 3锁定(全部导入) dic.Add("order_num", item.order_num); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); } else if (item.importtype == "销售开单") { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); string sql1 = "update tb_parts_sale_billing set is_occupy=@is_occupy where order_num=@order_num;"; dic.Add("is_occupy", !item.isfinish ? "2" : "3");//单据导入状态,0正常,1占用,2锁定(部分导入), 3锁定(全部导入) dic.Add("order_num", item.order_num); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); } } //更新前置单据中的各个配件的已完成数量 foreach (OrderFinishInfo item in list_orderinfo) { if (item.importtype == "销售订单") { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); string sql1 = "update tb_parts_sale_order_p set finish_count=@finish_count where sale_order_id=@sale_order_id and parts_code=@parts_code;"; dic.Add("finish_count", item.finish_num); dic.Add("sale_order_id", item.sale_order_id); dic.Add("parts_code", item.parts_code); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); } else if (item.importtype == "销售开单") { sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; dic = new Dictionary<string, string>(); string sql1 = "update tb_parts_sale_billing_p set finish_count=@finish_count where sale_billing_id=@sale_billing_id and parts_code=@parts_code;"; dic.Add("finish_count", item.finish_num); dic.Add("sale_billing_id", item.sale_order_id); dic.Add("parts_code", item.parts_code); sysStringSql.sqlString = sql1; sysStringSql.Param = dic; listSql.Add(sysStringSql); } } DBHelper.BatchExeSQLStringMultiByTrans("提交销售开单,更新引用的销售订单或销售开单的导入状态", listSql); }
/// <summary> /// 审核通过后需要自动生成的单子的方法 /// </summary> void CreateBill(List<string> listField) { try { if (listField.Count > 0) { string bill_ids = string.Empty; List<SysSQLString> listSql = new List<SysSQLString>(); DataTable TemplateTable = CommonFuncCall.CreatePartStatisticTable();//获取要填充的公用表 for (int i = 0; i < listField.Count; i++) { DataTable dt = DBHelper.GetTable("", "tb_parts_purchase_billing", "*", string.Format("purchase_billing_id='{0}'", listField[i]), "", ""); if (dt != null && dt.Rows.Count > 0) { DataTable dt_bill_p = new DataTable(); tb_parts_purchase_billing model = new tb_parts_purchase_billing(); CommonFuncCall.SetModlByDataTable(model, dt); #region 开单审核通过后,更新配件的账面库存 string str_time = Common.UtcLongToLocalDateTime(model.order_date).ToShortDateString(); long long_time = Common.LocalDateTimeToUtcLong(Convert.ToDateTime(str_time)); DataTable IOPartTable = DBHelper.GetTable("", "tb_parts_purchase_billing_p", "*", string.Format("purchase_billing_id='{0}'", listField[i]), "", ""); if (IOPartTable != null && IOPartTable.Rows.Count > 0) { for (int a = 0; a < IOPartTable.Rows.Count; a++) { DataRow dr = TemplateTable.NewRow();//创建模版表行项 dr["OrderDate"] = long_time.ToString();//单据日期 dr["WareHouseID"] = CommonCtrl.IsNullToString(IOPartTable.Rows[a]["wh_id"]);//仓库ID dr["WareHouseName"] = CommonCtrl.IsNullToString(IOPartTable.Rows[a]["wh_name"]);//仓库名称 dr["PartID"] = CommonCtrl.IsNullToString(IOPartTable.Rows[a]["parts_id"]);//配件ID dr["PartCode"] = IOPartTable.Rows[a]["parts_code"].ToString();//配件编码 dr["PartName"] = IOPartTable.Rows[a]["parts_name"].ToString();//配件名称 dr["PartSpec"] = IOPartTable.Rows[a]["model"].ToString();//配件规格 dr["PartBarCode"] = IOPartTable.Rows[a]["parts_barcode"].ToString();//配件条码 dr["CarPartsCode"] = IOPartTable.Rows[a]["car_factory_code"].ToString();//车厂编码 dr["DrawNum"] = IOPartTable.Rows[a]["drawing_num"].ToString();//配件图号 dr["UnitName"] = IOPartTable.Rows[a]["unit_name"].ToString();//单位名称 //dr["PartCount"] = IOPartTable.Rows[a]["business_counts"].ToString();//配件业务数量 dr["PartCount"] = Convert.ToDecimal(IOPartTable.Rows[a]["business_counts"].ToString());//配件业务数量 dr["StatisticType"] = (int)DataSources.EnumStatisticType.PaperCount;//统计类型 TemplateTable.Rows.Add(dr);//添加新的数据行项 } } #endregion #region 当金额大于0时,自动生成预付款单 if (model.this_payment > 0) { tb_bill_receivable a = new tb_bill_receivable(); tb_balance_documents b = new tb_balance_documents(); tb_payment_detail c = new tb_payment_detail(); a.cust_id = model.sup_id;//供应商ID a.order_num = CommonUtility.GetNewNo(DataSources.EnumProjectType.PAYMENT);//订单号 a.order_type = (int)DataSources.EnumOrderType.PAYMENT; a.payment_type = (int)DataSources.EnumPaymentType.PAYMENT; a.org_id = model.org_id; b.billing_money = model.this_payment;//开单金额 b.documents_date = model.order_date;//单据日期 b.documents_id = model.purchase_billing_id;//单据ID b.documents_name = "采购开单";//单据名称 b.documents_num = model.order_num;//单据编码 c.money = model.this_payment;//金额 c.balance_way = model.balance_way;//结算方式 c.check_number = model.check_number;//票号 DBOperation.AddBillReceivable(a, b, c); } #endregion #region 自动生产出入库单 #region 一:采购收货单(业务数量大于0)-->自动生成入库单 if (model.order_type == Convert.ToInt32(DataSources.EnumPurchaseOrderType.PurchaseReceive).ToString()) { string stock_inout_id = string.Empty; //1.生成入库单 if (CreateIntoStock("入库单", model, ref stock_inout_id)) { //2.查询配件信息,生成配件信息入库单 DataTable dt_parts = DBHelper.GetTable("查询采购开单配件信息表", "tb_parts_purchase_billing_p", "*", " purchase_billing_id='" + model.purchase_billing_id + "'", "", ""); if (dt_parts != null && dt_parts.Rows.Count > 0) { dt_bill_p = dt_parts; for (int a = 0; a < dt_parts.Rows.Count; a++) { tb_parts_purchase_billing_p bill_p_model = new tb_parts_purchase_billing_p(); CommonFuncCall.SetModlByDataTable(bill_p_model, dt_parts, a); bool ret = CreateIntoPartsStock(stock_inout_id, bill_p_model, model.purchase_billing_id, model.order_num); } } } } #endregion #region 二:采购退货(业务数量小于0)-->自动生成出库单 else if (model.order_type == Convert.ToInt32(DataSources.EnumPurchaseOrderType.PurchaseBack).ToString()) { string stock_inout_id = string.Empty; //1.生成出库单 if (CreateIntoStock("出库单", model, ref stock_inout_id)) { //2.查询配件信息,生成配件信息出库单 DataTable dt_parts = DBHelper.GetTable("查询采购开单配件信息表", "tb_parts_purchase_billing_p", "*", " purchase_billing_id='" + model.purchase_billing_id + "'", "", ""); if (dt_parts != null && dt_parts.Rows.Count > 0) { for (int a = 0; a < dt_parts.Rows.Count; a++) { tb_parts_purchase_billing_p bill_p_model = new tb_parts_purchase_billing_p(); CommonFuncCall.SetModlByDataTable(bill_p_model, dt_parts, a); bool ret = CreateIntoPartsStock(stock_inout_id, bill_p_model, model.purchase_billing_id, model.order_num); } } } } #endregion #region 三:采购换货-->自动生成出、入库单(数量大于0是入库,小于0是出库) else if (model.order_type == Convert.ToInt32(DataSources.EnumPurchaseOrderType.PurchaseExchange).ToString()) { string stock_in_id = string.Empty; string stock_out_id = string.Empty; //1.生成入库单 if (CreateIntoStock("入库单", model, ref stock_in_id)) { //2.查询配件入库数量大于0的,生成配件信息入库单 DataTable dt_parts = DBHelper.GetTable("查询采购开单配件信息表", "tb_parts_purchase_billing_p", "*", " purchase_billing_id='" + model.purchase_billing_id + "' and isnull(business_counts,0)>0 ", "", ""); if (dt_parts != null && dt_parts.Rows.Count > 0) { for (int a = 0; a < dt_parts.Rows.Count; a++) { tb_parts_purchase_billing_p bill_p_model = new tb_parts_purchase_billing_p(); CommonFuncCall.SetModlByDataTable(bill_p_model, dt_parts, a); bool ret = CreateIntoPartsStock(stock_in_id, bill_p_model, model.purchase_billing_id, model.order_num); } } } //3.生成出库单 if (CreateIntoStock("出库单", model, ref stock_out_id)) { //4.查询配件入库数量小于0的,生成配件信息出库单 DataTable dt_parts = DBHelper.GetTable("查询采购开单配件信息表", "tb_parts_purchase_billing_p", "*", " purchase_billing_id='" + model.purchase_billing_id + "' and isnull(business_counts,0)<0 ", "", ""); if (dt_parts != null && dt_parts.Rows.Count > 0) { for (int a = 0; a < dt_parts.Rows.Count; a++) { tb_parts_purchase_billing_p bill_p_model = new tb_parts_purchase_billing_p(); CommonFuncCall.SetModlByDataTable(bill_p_model, dt_parts, a); CreateIntoPartsStock(stock_out_id, bill_p_model, model.purchase_billing_id, model.order_num); } } } } #endregion #endregion #region 向宇通发送配送单号 if (!string.IsNullOrEmpty(model.ration_send_code)) { //DBHelper.WebServHandler("审核通过入库时发送配送单号到宇通系统", EnumWebServFunName.UpLoadPartPutStore, model.ration_send_code); DBHelper.WebServHandler("", EnumWebServFunName.LoadPartInStore, model.ration_send_code); } #endregion #region 审核通过时,将本次现付更新到已结算金额中 if (model.this_payment > 0) { SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数 dicParam.Add("balance_money", model.this_payment.ToString()); dicParam.Add("purchase_billing_id", listField[i]); StringBuilder sb = new StringBuilder(); sb.Append(" Update tb_parts_purchase_billing Set balance_money=@balance_money where purchase_billing_id=@purchase_billing_id"); sysStringSql.Param = dicParam; sysStringSql.sqlString = sb.ToString(); listSql.Add(sysStringSql); } #endregion #region 如果配送单号不为空,需将对应的配送单状态修改为“已收货” if (!string.IsNullOrEmpty(model.ration_send_code)) { SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数 dicParam.Add("distribution_status", "2");//1:配送中,2:已收货 dicParam.Add("ration_send_code", model.ration_send_code); StringBuilder sb = new StringBuilder(); sb.Append(" Update tb_distribution Set distribution_status=@distribution_status where ration_send_code=@ration_send_code"); sysStringSql.Param = dicParam; sysStringSql.sqlString = sb.ToString(); listSql.Add(sysStringSql); } #endregion #region 如果配送单号不为空,需通过配送单将对应的宇通采购订单的确认数量修改下 // if (!string.IsNullOrEmpty(model.ration_send_code)) // { // if (dt_bill_p != null && dt_bill_p.Rows.Count > 0) // { // dt_bill_p = DBHelper.GetTable("查询采购开单配件信息表", "tb_parts_purchase_billing_p", "*", " purchase_billing_id='" + model.purchase_billing_id + "'", "", ""); // if (dt_bill_p != null && dt_bill_p.Rows.Count > 0) // { // string wherestr = string.Format(@"purchase_order_yt_id in // ( // select top 1 purchase_order_yt_id from tb_parts_purchase_order_2 where dsn_adjustable_parts in // ( // select top 1 dsn_adjustable_parts from tb_distribution where ration_send_code='{0}' // ) // )", model.ration_send_code); // DataTable dt_yt_p = DBHelper.GetTable("采购开单审核通过时,修改宇通采购订单配件信息量", "tb_parts_purchase_order_p_2", "*", wherestr, "", ""); // if (dt_yt_p != null && dt_yt_p.Rows.Count > 0) // { // foreach (DataRow dr in dt_bill_p.Rows) // { // DataRow[] dr_yt_p = dt_yt_p.Select("parts_code ='" + dr["parts_code"].ToString() + "'"); // if (dr_yt_p.Length > 0) // { // decimal old_conf_count = Convert.ToDecimal(dr_yt_p[0]["conf_count"].ToString()); // decimal new_conf_count = Convert.ToDecimal(dr["business_counts"].ToString()); // SysSQLString sysStringSql = new SysSQLString(); // sysStringSql.cmdType = CommandType.Text; // Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数 // dicParam.Add("conf_count", (old_conf_count + new_conf_count).ToString()); // dicParam.Add("purchase_order_yt_id", dr_yt_p[0]["purchase_order_yt_id"].ToString()); // dicParam.Add("parts_code", dr_yt_p[0]["parts_code"].ToString()); // StringBuilder sb = new StringBuilder(); // sb.Append(" Update tb_parts_purchase_order_p_2 Set conf_count=@conf_count where purchase_order_yt_id=@purchase_order_yt_id and parts_code=@parts_code"); // sysStringSql.Param = dicParam; // sysStringSql.sqlString = sb.ToString(); // listSql.Add(sysStringSql); // } // } // } // } // } // } #endregion } } CommonFuncCall.StatisticStock(TemplateTable, "采购开单审核通过后更新配件的账面库存"); //审核通过时,将本次现付更新到已结算金额中 if (listSql.Count > 0) { DBHelper.BatchExeSQLStringMultiByTrans("采购开单审核通过时,将本次现付更新到已结算金额中或是更新配送单状态", listSql); } } } catch (Exception ex) { } }
/// <summary> /// 添加情况下组装sql的方法 /// </summary> /// <param name="listSql"></param> /// <param name="sale_billing_id"></param> private void AddSaleOrderSqlString(List<SysSQLString> listSql, string sale_billing_id, string HandleType) { decimal allmoney = 0; SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数 ddtorder_date.Value = Convert.ToDateTime(ddtorder_date.Value.ToShortDateString() + " 23:59:59"); ddtreceivables_date.Value = Convert.ToDateTime(ddtreceivables_date.Value.ToShortDateString() + " 23:59:59"); tb_parts_sale_billing model = new tb_parts_sale_billing(); CommonFuncCall.SetModelObjectValue(this, model); GetAllMoney(ref allmoney); model.sale_billing_id = sale_billing_id; model.cust_id = cust_id; model.cust_code = cust_code; model.cust_name = txtcust_name.Text; //单据类型 if (!string.IsNullOrEmpty(ddlorder_type.SelectedValue.ToString())) { model.order_type_name = ddlorder_type.SelectedItem.ToString(); } //发票类型 if (!string.IsNullOrEmpty(ddlreceipt_type.SelectedValue.ToString())) { model.receipt_type_name = ddlreceipt_type.SelectedItem.ToString(); } //运输方式 if (!string.IsNullOrEmpty(ddltrans_way.SelectedValue.ToString())) { model.trans_way_name = ddltrans_way.SelectedItem.ToString(); } //结算方式 if (!string.IsNullOrEmpty(ddlbalance_way.SelectedValue.ToString())) { model.balance_way_name = ddlbalance_way.SelectedItem.ToString(); } //结算账户 if (!string.IsNullOrEmpty(ddlbalance_account.SelectedValue.ToString())) { model.balance_account_name = ddlbalance_account.SelectedItem.ToString(); } //部门 if (!string.IsNullOrEmpty(ddlorg_id.SelectedValue.ToString())) { model.org_id = ddlorg_id.SelectedValue.ToString(); model.org_name = ddlorg_id.SelectedItem.ToString(); } //经办人 if (!string.IsNullOrEmpty(ddlhandle.SelectedValue.ToString())) { model.handle = ddlhandle.SelectedValue.ToString(); model.handle_name = ddlhandle.SelectedItem.ToString(); } model.balance_unit = balance_unit; model.balance_unit_name = txtbalance_unit.Text.Trim(); model.create_by = GlobalStaticObj.UserID; model.create_name = GlobalStaticObj.UserName; model.create_time = Common.LocalDateTimeToUtcLong(DateTime.Now); model.operators = GlobalStaticObj.UserID; model.operator_name = GlobalStaticObj.UserName; model.com_id = GlobalStaticObj.CurrUserCom_Id;//公司ID model.com_code = GlobalStaticObj.CurrUserCom_Code;//公司编码 model.com_name = GlobalStaticObj.CurrUserCom_Name;//公司名称 model.allmoney = allmoney; model.is_occupy = "0"; model.is_lock = "0"; model.enable_flag = "1"; if (HandleType == "保存") { model.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.DRAFT).ToString(); model.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.DRAFT, true); } else if (HandleType == "提交") { model.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.SUBMIT).ToString(); model.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.SUBMIT, true); } if (model != null) { StringBuilder sb = new StringBuilder(); sb.Append(" Insert Into tb_parts_sale_billing( "); StringBuilder sp = new StringBuilder(); StringBuilder sb_prame = new StringBuilder(); foreach (PropertyInfo info in model.GetType().GetProperties()) { string name = info.Name; object value = info.GetValue(model, null); sb_prame.Append("," + name); sp.Append(",@" + name); dicParam.Add(name, value == null ? "" : value.ToString()); } sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values ("); sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")").Append(";"); sysStringSql.sqlString = sb.ToString(); sysStringSql.Param = dicParam; listSql.Add(sysStringSql); } }
/// <summary> /// 编辑情况下组装sql的方法 /// </summary> /// <param name="listSql"></param> /// <param name="sale_billing_id"></param> /// <param name="model"></param> private void EditSaleOrderSqlString(List<SysSQLString> listSql, string sale_billing_id, tb_parts_sale_billing model, string HandleType) { decimal allmoney = 0; SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数 ddtorder_date.Value = Convert.ToDateTime(ddtorder_date.Value.ToShortDateString() + " 23:59:59"); ddtreceivables_date.Value = Convert.ToDateTime(ddtreceivables_date.Value.ToShortDateString() + " 23:59:59"); CommonFuncCall.SetModelObjectValue(this, model); GetAllMoney(ref allmoney); model.cust_id = cust_id; model.cust_code = cust_code; model.cust_name = txtcust_name.Text; //单据类型 if (!string.IsNullOrEmpty(ddlorder_type.SelectedValue.ToString())) { model.order_type_name = ddlorder_type.SelectedItem.ToString(); } //发票类型 if (!string.IsNullOrEmpty(ddlreceipt_type.SelectedValue.ToString())) { model.receipt_type_name = ddlreceipt_type.SelectedItem.ToString(); } //运输方式 if (!string.IsNullOrEmpty(ddltrans_way.SelectedValue.ToString())) { model.trans_way_name = ddltrans_way.SelectedItem.ToString(); } //结算方式 if (!string.IsNullOrEmpty(ddlbalance_way.SelectedValue.ToString())) { model.balance_way_name = ddlbalance_way.SelectedItem.ToString(); } //结算账户 if (!string.IsNullOrEmpty(ddlbalance_account.SelectedValue.ToString())) { model.balance_account_name = ddlbalance_account.SelectedItem.ToString(); } //部门 if (!string.IsNullOrEmpty(ddlorg_id.SelectedValue.ToString())) { model.org_id = ddlorg_id.SelectedValue.ToString(); model.org_name = ddlorg_id.SelectedItem.ToString(); } //经办人 if (!string.IsNullOrEmpty(ddlhandle.SelectedValue.ToString())) { model.handle = ddlhandle.SelectedValue.ToString(); model.handle_name = ddlhandle.SelectedItem.ToString(); } model.balance_unit = balance_unit; model.balance_unit_name = txtbalance_unit.Text.Trim(); model.update_by = GlobalStaticObj.UserID; model.update_name = GlobalStaticObj.UserName; model.update_time = Common.LocalDateTimeToUtcLong(DateTime.Now); model.operators = GlobalStaticObj.UserID; model.operator_name = GlobalStaticObj.UserName; model.allmoney = allmoney; model.enable_flag = "1"; if (HandleType == "保存") { model.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.DRAFT).ToString(); model.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.DRAFT, true); } else if (HandleType == "提交") { model.order_status = Convert.ToInt32(DataSources.EnumAuditStatus.SUBMIT).ToString(); model.order_status_name = DataSources.GetDescription(DataSources.EnumAuditStatus.SUBMIT, true); } if (model != null) { StringBuilder sb = new StringBuilder(); sb.Append(" Update tb_parts_sale_billing Set "); bool isFirstValue = true; foreach (PropertyInfo info in model.GetType().GetProperties()) { string name = info.Name; object value = info.GetValue(model, null); if (isFirstValue) { isFirstValue = false; sb.Append(name); sb.Append("="); sb.Append("@" + name); } else { sb.Append("," + name); sb.Append("="); sb.Append("@" + name); } dicParam.Add(name, value == null ? "" : value.ToString()); } sb.Append(" where sale_billing_id='" + sale_billing_id + "';"); sysStringSql.sqlString = sb.ToString(); sysStringSql.Param = dicParam; listSql.Add(sysStringSql); } }
/// <summary> /// 添加仓库档案的sql语句 /// </summary> /// <param name="listSql"></param> /// <param name="partID"></param> private void AddWareHouseSqlString(List<SysSQLString> listSql, string whId) { SysSQLString sysStringSql = new SysSQLString(); sysStringSql.cmdType = CommandType.Text; Dictionary<string, string> dicParam = new Dictionary<string, string>();//参数 tb_warehouse model = new tb_warehouse(); CommonFuncCall.SetModelObjectValue(this, model); model.wh_id = whId; int datasorces = (int)SYSModel.DataSources.EnumDataSources.SELFBUILD;//1自建,2宇通 model.data_source = datasorces.ToString(); int StatusNum = (int)SYSModel.DataSources.EnumStatus.Start;//0停用,1启用 model.status = StatusNum.ToString(); model.enable_flag = "1"; model.create_by = GlobalStaticObj.UserID; model.create_time = Common.LocalDateTimeToUtcLong(DateTime.Now); model.com_id = txtChooseCompanty.Tag == null ? "" : txtChooseCompanty.Tag.ToString(); model.wh_head = txtChooseWhHead.Text; if (model != null) { StringBuilder sb = new StringBuilder(); sb.Append(" Insert Into tb_warehouse( "); StringBuilder sp = new StringBuilder(); StringBuilder sb_prame = new StringBuilder(); foreach (PropertyInfo info in model.GetType().GetProperties()) { string name = info.Name; object value = info.GetValue(model, null); sb_prame.Append("," + name); sp.Append(",@" + name); dicParam.Add(name, value == null ? "" : value.ToString()); } sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values ("); sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")").Append(";"); sysStringSql.sqlString = sb.ToString(); sysStringSql.Param = dicParam; listSql.Add(sysStringSql); } }