MVC大型商贸系统(库存管理)技术解释(四) 商品调拨
1、 商品调拨主界面
2、 代码
2.1、商品调拨html代码:
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html" charset="utf-8" />
<meta content="text/javascript;charset=utf-8" />
<title>Main</title>
<link href="../../Content/yxsss/js_css/yu.css" rel="stylesheet" />
<link href="../../Content/jquery-easyui-1.3.3/themes/icon.css" rel="stylesheet" />
<link href="../../Content/jquery-easyui-1.3.3/demo/demo.css" rel="stylesheet" />
<link href="../../Content/jquery-easyui-1.3.3/themes/default/easyui.css" rel="stylesheet" />
<link href="../../Content/MyCSS/DealWithAllot.css" rel="stylesheet" />
<script src="../../Content/yxsss/js_css/yu.js"></script>
<script src="../../Content/jquery-easyui-1.3.3/jquery.min.js"></script>
<script src="../../Content/jquery-easyui-1.3.3/jquery.easyui.min.js"></script>
<script src="../../Scripts/MyScript/DealWithAllot.js"></script>
<style type="text/css">
.table5 tr > td, .table5 { font-size: 110%; }
</style>
</head>
<body οnlοad="AddRunningDiv();" style="text-align: center;">
<div style="display: none">
<div class="easyui-window" id="wRecord_number" title="调拨记录编号" style="width: 404px; height: 246px; top: 60px; left: 90px;" data-options="closed:true,maximizable:false,minimizable:false,modal:true">
<div class="easyui-panel" style="width: 390px; height: 180px; border: none">
<table class="easyui-datagrid" id="tbJiLuBianHao" style="border: none; border-spacing: 0; height: 178px;" data-options="idField:'Allot_id',
onDblClickRow:onDblBanHaoRow,singleSelect:true,rownumbers:true,striped:true,loadMsg:''">
<thead>
<tr>
<th data-options="field:'Record_number',width:120,align:'center'">记录编号</th>
<th data-options="field:'Consignment_department',width:100,align:'center'">发货部门</th>
<th data-options="field:'Receiving_department',width:100,align:'center'">收货部门</th>
<th data-options="field:'Receiving_department_id',hidden:true">收货部门id</th>
</tr>
</thead>
</table>
</div>
<div style="width: 390px; font-size: 17px;">
请输入记录编号:<input type="text" οnkeyup="selectAllotByBlur($('#txtRecord_number').val());" id="txtRecord_number" style="width: 200px;" />
</div>
</div>
<div class="easyui-window" id="wBatch" style="width: 640px; height: 453px;" title="调整商品配货批次" data-options="closed:true,maximizable:false,minimizable:false,modal:true">
<table style="border: none; height: 380px; width: 591px; padding: 0;">
<tr>
<td style="width: 260px;">
<div class="easyui-panel" style="width: 260px; height: 380px; border: #cccccc">
<table class="easyui-datagrid" id="tbBatch1" style="width: 1400px; border: none" data-options="rownumbers:true,fit:true,singleSelect:true,
idField:'Allot_detail_id',onClickRow:onGoodsSelect">
<thead>
<tr>
<th data-options="field:'Goods_name',width:80,align:'center'">商品名称</th>
<th data-options="field:'Shipments_quantity',width:75,align:'center'">发货细数</th>
<th data-options="field:'Repertory_place_send',align:'center',width:120">发货库存地点</th>
<th data-options="field:'Goods_bar_code',width:80,align:'center'">商品条码</th>
<th data-options="field:'Goods_code',width:80,align:'center'">商品代码</th>
<th data-options="field:'Repertory_enter_packages',width:75,align:'center'">发货件数</th>
<th data-options="field:'Repertory_place_harvest',align:'center',width:120">收货库存地点</th>
<th data-options="field:'Tax_inclusive_price',width:80,align:'center'">含税进价</th>
<th data-options="field:'Goods_abbreviation',width:80,align:'center'">商品简称</th>
<th data-options="field:'Quality_content',width:55 ,align:'center'">包装含量</th>
<th data-options="field:'Art_No',width:80,align:'center'">货号</th>
<th data-options="field:'Retail_unit_price',width:80,align:'center'">零售单价</th>
<th data-options="field:'Vender_bar_code_deny',width:80,align:'center'">厂家条码否</th>
<th data-options="field:'Format_model',width:80,align:'center'">规格型号</th>
<th data-options="field:'Plncode_PLN',width:80,align:'center'">PLN码</th>
<th data-options="field:'Goods_tab',width:80,align:'center'">商品标记</th>
<th data-options="field:'Copy_record_deny',width:80,align:'center'">复制记录否</th>
<th data-options="field:'Contract_number',width:80,align:'center'">合同号</th>
<th data-options="field:'Manufacturer_name',width:80,align:'center'">生产厂家</th>
<th data-options="field:'Producing_area_name',width:80,align:'center'">产地</th>
<th data-options="field:'Unit_of_measurement',width:80,align:'center'">计量单位</th>
<th data-options="field:'Use_target',width:80,align:'center'">使用对象</th>
<th data-options="field:'Goods_colours',width:80,align:'center'">商品花色</th>
<th data-options="field:'Chinese',width:80,align:'center'">供应单位</th>
<th data-options="field:'Goods_classify',width:80,align:'center'">商品分类</th>
<th data-options="field:'Quality_guarantee_period',width:80,align:'center'">保质期</th>
<th data-options="field:'Input_ratio',width:80,align:'center'">进项税率</th>
<th data-options="field:'Output_ratio',width:80,align:'center'">销项税率</th>
<th data-options="field:'Count_scale',width:80,align:'center'">统计比例</th>
<th data-options="field:'Quality_grade',width:80,align:'center'">质量等级</th>
<th data-options="field:'Manage_season_name',width:80,align:'center'">经营季节</th>
<th data-options="field:'Permit_decimal_deny',width:80,align:'center'">允许销售否</th>
<th data-options="field:'Product_status',width:80,align:'center'">产品状态</th>
<th data-options="field:'Purchase_bid',width:80,align:'center'">采购进价</th>
<th data-options="field:'Self_fix_goods_deny',width:80,align:'center'">自订货否</th>
<th data-options="field:'Mini_fix_goods_deny',width:80,align:'center'">最小订货数</th>
<th data-options="field:'Goods_id',hidden:true"></th>
<th data-options="field:'Repertory_place_send_id',hidden:true"></th>
</tr>
</thead>
</table>
</div>
</td>
<td style="width: 356px;">
<div class="easyui-panel" style="width: 356px; height: 380px;">
<table class="easyui-datagrid" id="tbBatch2" style="width: 700px; border: none" data-options="rownumbers:true,fit:true,singleSelect:true,
idField:'Patch_id'">
<thead>
<tr>
<th data-options="field:'Patch_number',width:110,align:'center'">批次</th>
<th data-options="field:'Delivery_quantity',editor:{type:'numberbox'},width:80,align:'center'">配货数量</th>
<th data-options="field:'Stock_quantity',width:80,align:'center'">结存数量</th>
<th data-options="field:'Purchase_bid',width:90,align:'center'">不含税进价</th>
<th data-options="field:'InputTax',width:90,align:'center'">含税进价</th>
</tr>
</thead>
</table>
</div>
</td>
</tr>
</table>
<div style="display: table-cell; vertical-align: middle; text-align: center; width: 625px; height: 30px;">
<button οnclick="ziDongShengChenPiCi();" style="width: 200px;">自动生成配货批次</button>
<button οnclick="updateBatch()">
<img src="../../Content/image/Note-Memo.png" />修改</button>
<button οnclick="updatePatch();">
<img src="../../Content/image/Check.png" />确认</button>
<button οnclick="$('#tbBatch2').datagrid('rejectChanges');">
<img src="../../Content/image/Clean.png" />取消</button>
<button οnclick="$('#wBatch').window('close')">
<img src="../../Content/image/Out.png" />退出</button>
</div>
</div>
<div class="easyui-window" id="wAllGoods" title="全部商品信息" style="width: 700px; height: 391px; top: 100px;" data-options="closed:true,maximizable:false,minimizable:false,modal:true">
<div class="easyui-panel" style="width: 686px; height: 327px;">
<table id="tbGoodsAll" class="easyui-datagrid" style="width: 3360px;" data-options="rownumbers:true,
striped:true,idField:'Goods_id',fit:true,fitColumns:false,frozenColumns:[[{field:'XuanZhe',checkbox:true}]],
striped:true,loadMsg:'',checkOnSelect:true,selectOnCheck:true">
<thead>
<tr>
<th data-options="field:'Goods_code',width:80,align:'center'">商品代码</th>
<th data-options="field:'Goods_name',width:80,align:'center'">商品名称</th>
<th data-options="field:'Goods_bar_code',width:80,align:'center'">商品条码</th>
<th data-options="field:'Vender_bar_code_deny',width:80,align:'center'">厂家条码否</th>
<th data-options="field:'Quality_content',width:80,align:'center'">包装含量</th>
<th data-options="field:'Retail_unit_price',width:80,align:'center'">零售单价</th>
<th data-options="field:'Format_model',width:80,align:'center'">规格型号</th>
<th data-options="field:'Goods_abbreviation',width:80,align:'center'">商品简称</th>
<th data-options="field:'Plncode_PLN',width:80,align:'center'">PLN码</th>
<th data-options="field:'Goods_tab',width:80,align:'center'">商品标记</th>
<th data-options="field:'Copy_record_deny',width:80,align:'center'">复制记录否</th>
<th data-options="field:'Contract_number',width:80,align:'center'">合同号</th>
<th data-options="field:'Manufacturer_name',width:80,align:'center'">生产厂家</th>
<th data-options="field:'Producing_area_name',width:80,align:'center'">产地</th>
<th data-options="field:'Art_No',width:80,align:'center'">货号</th>
<th data-options="field:'Unit_of_measurement',width:80,align:'center'">计量单位</th>
<th data-options="field:'Use_target',width:80,align:'center'">使用对象</th>
<th data-options="field:'Goods_colours',width:80,align:'center'">商品花色</th>
<th data-options="field:'Chinese',width:80,align:'center'">供应单位</th>
<th data-options="field:'Goods_rademark_name',width:80,align:'center'">商品商标</th>
<th data-options="field:'Goods_classify',width:80,align:'center'">商品分类</th>
<th data-options="field:'Quality_guarantee_period',width:80,align:'center'">保质期</th>
<th data-options="field:'Input_ratio',width:80,align:'center'">进项税率</th>
<th data-options="field:'Output_ratio',width:80,align:'center'">销项税率</th>
<th data-options="field:'Count_scale',width:80,align:'center'">统计比例</th>
<th data-options="field:'Quality_content',width:80,align:'center'">质地含量</th>
<th data-options="field:'Quality_grade',width:80,align:'center'">质量等级</th>
<th data-options="field:'Manage_season_name',width:80,align:'center'">经营季节</th>
<th data-options="field:'Permit_decimal_deny',width:80,align:'center'">允许销售否</th>
<th data-options="field:'Product_status',width:80,align:'center'">产品状态</th>
<th data-options="field:'Retail_unit_price',width:80,align:'center'">零售单价</th>
<th data-options="field:'Purchase_bid',width:80,align:'center'">采购进价</th>
<th data-options="field:'Self_fix_goods_deny',width:80,align:'center'">自订货否</th>
<th data-options="field:'Mini_fix_goods_deny',width:80,align:'center'">最小订货数</th>
</tr>
</thead>
</table>
</div>
<div class="tbga" style="width: 100%; font-size: 16px;">
请输入商品名称/代码/条码查找:<input οnkeyup="moHiuChaXun($('#moHiuChaXun').val())" type="text" id="moHiuChaXun" style="width: 150px; height: 15px;" />
<button οnclick="$('#tbGoodsAll').datagrid('checkAll')">全选</button>
<button οnclick="$('#tbGoodsAll').datagrid('uncheckAll')">全不选</button>
<button οnclick="getChangPin();">添加</button>
</div>
</div>
<div class="easyui-window" id="wShipper" style="width: 390px; height: 200px; top: 60px; left: 420px;" data-options="closed:true,maximizable:false,minimizable:false,modal:true">
<table class="easyui-datagrid" id="tbShipper" data-options="idField:'Employee_id',fit:true,singleSelect:true,onDblClickRow:onDblClicktbShipper">
<thead>
<tr>
<th data-options="field:'Employee_number',width:80,align:'center'">编号</th>
<th data-options="field:'Employee_name',width:80,align:'center'">姓名</th>
<th data-options="field:'sex',width:40,align:'center'">性别</th>
<th data-options="field:'Department_name',width:80,align:'center'">所属部门</th>
<th data-options="field:'Duties_name',width:70,align:'center'">所任职务</th>
</tr>
</thead>
</table>
</div>
<div class="easyui-window" id="wReceiver" style="width: 390px; height: 200px; top: 60px; left: 420px;" data-options="closed:true,maximizable:false,minimizable:false,modal:true">
<table class="easyui-datagrid" id="tbReceiver" data-options="idField:'Employee_id',fit:true,singleSelect:true,onDblClickRow:onDblClicktbReceiver">
<thead>
<tr>
<th data-options="field:'Employee_number',width:80,align:'center'">编号</th>
<th data-options="field:'Employee_name',width:80,align:'center'">姓名</th>
<th data-options="field:'sex',width:40,align:'center'">性别</th>
<th data-options="field:'Department_name',width:80,align:'center'">所属部门</th>
<th data-options="field:'Duties_name',width:70,align:'center'">所任职务</th>
</tr>
</thead>
</table>
</div>
<div class="easyui-window" id="wdiaoBo" title="" style="display: none; text-align: center; border-spacing: 0; padding: 0; border: 0px solid white"
data-options="fit:true,collapsible:false,minimizable:false,maximizable:false,closable:false,draggable:false,resizable:false,shadow:false,modal:true,center:true">
<div style="padding: 0; background: #e0ffff; color: #4169e1; font-size: 20px; margin: 0 0 auto;">
<span style="float: right">
<img οnclick="parent.allot();" id="dimg" src="../../Content/image/delete.png" /></span>调拨单处理
</div>
<div class="easyui-panel" style="margin-left: 1px; margin-right: 1px; height: 25px; width: 846px; border: none; text-align: center;">
<button class="button1" id="btnJiLuBianHao" οnclick="openjiLuBianHao();" style="width: 100px;">记录编号</button><input type="text" id="Record_number" readonly="r" style="width: 120px; color: red" />
<button class="button1" id="btnFuHuoRen" οnclick="openwShipper(); " style="width: 100px;">实物付货人</button><input id="txtShiFuHuoRenMC" readonly="readonly" type="text" style="width: 120px; text-align: center; border: 1px solid black; border-style: none none solid none; font-size: 15px; color: blue" data-options="required: true" />
<button class="button1" id="btnShouHuoRen" οnclick="openwReceiver(); " style="width: 100px;">实物收货人</button><input id="txtShiShouHuoRenMC" readonly="readonly" type="text" style="width: 120px; text-align: center; border: 1px solid black; border-style: none none solid none; font-size: 15px; color: blue" data-options="required: true" />
</div>
<table class="table5" style="height: 70px; border-spacing: 4px; border: none">
<tr>
<td>发货部门:<input id="cbo_ConsignmentDepartmentCode" class="easyui-combotree" style="width: 200px;" data-options="onChange:onConsignmentDepartmentCodeSelect,required:true,missingMessage: '必选项!'" />
</td>
<td>
<div style="border-bottom: #000 1px solid; width: 500px;">
<input id="txtConsignmentDepartmentName" readonly="readonly" style="width: 500px; border: none; color: #0000FF; font-size: 16px;" />
</div>
</td>
</tr>
<tr>
<td>收货部门:<input id="cboReceivingDepartmentCode" class="easyui-combobox" style="width: 200px;" data-options=" onChange:onShouHuoBuMenSelect,required:true,missingMessage: '必选项!'" /></td>
<td>
<div style="border-bottom: #000 1px solid; width: 500px;">
<input id="txtReceivingDepartmentName" readonly="readonly" style="width: 500px; border: none; color: #0000FF; font-size: 16px;" />
</div>
</td>
</tr>
</table>
<div class="easyui-panel " style="width: 853px; margin-left: 1px; margin-right: 1px; height: 24px; border: none">
<button class="button1" style="width: 100px;" id="piLiangSuanZheShangPin" οnclick="openwAllGoods();">批量选择商品</button>
<button class="button1" style="width: 100px;" οnclick="openwShangPinPiCi();" style="margin: 0 148px 0 250px;">商品配货批次</button>
备注:<input id="txtBeiZhu" type="text" value="" style="width: 190px;" />
</div>
<div class="easyui-panel div1" style="width: 853px; height: 350px; border: 1px solid #cccccc">
<table id="tbSelectGoods" class="easyui-datagrid" style="width: 3850px; border: 1px solid #cccccc" data-options="rownumbers:true,idField:'Goods_id',singleSelect:true,
fit:true,frozenColumns:[[
{field:'ShanChu',width:40,title:'删除',align:'center',formatter:deleteSelect}
]],striped:true,loadMsg:'正在加载,请稍侯...'">
<thead>
<tr>
<th data-options="field:'Goods_code',width:80,align:'center'">商品代码</th>
<th data-options="field:'Goods_bar_code',width:80,align:'center'">商品条码</th>
<th data-options="field:'Goods_name',width:80,align:'center'">商品名称</th>
<th data-options="field:'Repertory_enter_packages',editor:{type:'numberbox',options:{value:0}},width:75,align:'center'">发货件数</th>
<th data-options="field:'Repertory_place_send_id',align:'center',width:100,
formatter:function(value,row){
return row.Repertory_place_send;
},
editor:{
type:'combobox',
options:{
valueField:'Repertory_place_send_id',
textField:'Repertory_place_send',
panelHeight:120
}}">发货库存地点</th>
<th data-options="field:'Repertory_place_harvest_id',align:'center',width:100,
formatter:function(value,row){
return row.Repertory_place_harvest;
},
editor:{
type:'combobox',
options:{
valueField: 'Repertory_place_harvest_id',
textField: 'Repertory_place_harvest',
panelHeight:120
}}">收货库存地点</th>
<th data-options="field:'Shipments_quantity',width:75,align:'center',editor:{type:'numberbox',options:{disabled:true}}">发货细数</th>
<th data-options="field:'StockQuantity',width:80,align:'center'">库存数</th>
<th data-options="field:'Tax_inclusive_price',width:80,align:'center',editor:{type:'numberbox',options:{prefix:'¥',precision:2,disabled:true}}">含税进价</th>
<th data-options="field:'Quality_content',width:55 ,align:'center'">包装含量</th>
<th data-options="field:'Purchase_bid',width:80,align:'center'">采购进价</th>
<th data-options="field:'Goods_abbreviation',width:80,align:'center'">商品简称</th>
<th data-options="field:'Input_ratio',width:80,align:'center'">进项税率</th>
<th data-options="field:'Art_No',width:80,align:'center'">货号</th>
<th data-options="field:'Retail_unit_price',width:80,align:'center'">零售单价</th>
<th data-options="field:'Vender_bar_code_deny',width:80,align:'center'">厂家条码否</th>
<th data-options="field:'Format_model',width:80,align:'center'">规格型号</th>
<th data-options="field:'Plncode_PLN',width:80,align:'center'">PLN码</th>
<th data-options="field:'Goods_tab',width:80,align:'center'">商品标记</th>
<th data-options="field:'Copy_record_deny',width:80,align:'center'">复制记录否</th>
<th data-options="field:'Contract_number',width:80,align:'center'">合同号</th>
<th data-options="field:'Manufacturer_name',width:80,align:'center'">生产厂家</th>
<th data-options="field:'Producing_area_name',width:80,align:'center'">产地</th>
<th data-options="field:'Unit_of_measurement',width:80,align:'center'">计量单位</th>
<th data-options="field:'Use_target',width:80,align:'center'">使用对象</th>
<th data-options="field:'Goods_colours',width:80,align:'center'">商品花色</th>
<th data-options="field:'Chinese',width:80,align:'center'">供应单位</th>
<th data-options="field:'Goods_classify',width:80,align:'center'">商品分类</th>
<th data-options="field:'Quality_guarantee_period',width:80,align:'center'">保质期</th>
<th data-options="field:'Output_ratio',width:80,align:'center'">销项税率</th>
<th data-options="field:'Count_scale',width:80,align:'center'">统计比例</th>
<th data-options="field:'Quality_grade',width:80,align:'center'">质量等级</th>
<th data-options="field:'Manage_season_name',width:80,align:'center'">经营季节</th>
<th data-options="field:'Permit_decimal_deny',width:80,align:'center'">允许销售否</th>
<th data-options="field:'Product_status',width:80,align:'center'">产品状态</th>
<th data-options="field:'Self_fix_goods_deny',width:80,align:'center'">自订货否</th>
<th data-options="field:'Mini_fix_goods_deny',width:80,align:'center'">最小订货数</th>
<th data-options="field:'Allot_detail_id',hidden:true"></th>
</tr>
</thead>
</table>
</div>
<div style="border: 0.001px solid blue">
<table class="table5" style="text-align: center; width: 850px; height: 60px; border: none">
<tr>
<td style="width: 155px; text-align: right;">制单人:
</td>
<td style="width: 125px; text-align: left;">
<div style="border-bottom: #f5f5f5 1px solid; width: 150px;">
<input id="cboMakeBills" class="easyui-combobox" data-options="required:true,missingMessage: '必选项!',onChange:selMb" style="width: 150px; border: 1px; color: #0000FF" />
</div>
</td>
<td style="width: 90px; text-align: right">执行人:
</td>
<td style="width: 165px; text-align: left;">
<div style="border-bottom: #f5f5f5 1px solid; width: 150px;">
<input class="easyui-combobox" id="cboTransactor" data-options="required:true,missingMessage: '必选项!',onSelect:selTs" style="width: 150px; color: #0000FF" />
</div>
</td>
</tr>
<tr>
<td style="width: 155px; text-align: right;">制单时间:
</td>
<td>
<div style="border-bottom: #f5f5f5 1px solid; width: 150px;">
<input class="easyui-datetimebox" id="dtmMakeBills" data-options="formatter:myformatter,required:true,missingMessage: '必选项!'" style="width: 150px; height: 20px; border: none; color: #0000FF" />
</div>
</td>
<td style="text-align: right;">执行时间:
</td>
<td>
<div style="border-bottom: #f5f5f5 1px solid; width: 150px;">
<input class="easyui-datetimebox" id="dtmExecute" data-options="formatter:myformatter,required:true,missingMessage: '必选项!'" style="width: 150px; height: 20px; border: none; color: #0000FF" />
</div>
</td>
</tr>
</table>
</div>
<div>
<table style="width: 850px;">
<tr>
<td>
<div style="width: 85px; height: 25px; text-align: center; vertical-align: middle">
<button id="TianJia" οnclick="tianJia(); ">
<img src="../../Content/image/Add-New.png" />新增
</button>
</div>
</td>
<td>
<div style="width: 85px; height: 25px; text-align: center; vertical-align: middle">
<button id="XiuGai" οnclick="xiuGai();">
<img src="../../Content/image/Note-Memo.png" />修改</button>
</div>
</td>
<td>
<div style="width: 85px; height: 25px; text-align: center; vertical-align: middle">
<button id="ShanChu" οnclick="shanChu();">
<img src="../../Content/image/Delete.png" />删除</button>
</div>
</td>
<td>
<div style="width: 85px; height: 25px; text-align: center; vertical-align: middle">
<button id="BaoCun" οnclick="saveDiaoBoDan();">
<img src="../../Content/image/Save.png" />保存
</button>
</div>
</td>
<td>
<div style="width: 85px; height: 25px; text-align: center; vertical-align: middle">
<button id="FanQi" οnclick="fanQi();">
<img src="../../Content/image/Clean.png" />放弃</button>
</div>
</td>
<td>
<div style="width: 85px; height: 25px; text-align: center; vertical-align: middle">
<button id="ShenHe" οnclick="reViewDiaoBoDan()">
<img src="../../Content/image/Principal-02.png" />审核</button>
</div>
</td>
<td>
<div style="width: 85px; height: 25px; text-align: center; vertical-align: middle">
<button id="DaYin" οnclick="DaYin()">
<img src="../../Content/image/Printer.png" />打印</button>
</div>
</td>
<td>
<div style="width: 85px; height: 25px; text-align: center; vertical-align: middle">
<button id="TuiChu" οnclick="parent.allot();">
<img src="../../Content/image/Out.png" />退出
</button>
</div>
</td>
</tr>
</table>
</div>
</div>
</div>
</body>
</html>
2.2、商品调拨JavaScript代码:
$(document).ready(function () {
$("#wdiaoBo").show();//显示wdiaoBo窗体
tianJia();//调用新增方法创建新增环境
tbSelectGoods();
$(".easyui-combobox").combobox({ missingMessage: '必选项!' });
$("#tbSelectGoods").datagrid("hideColumn", 'StockQuantity');
});
//切换编辑方法
var editIndex = undefined; //行索引
function tbSelectGoods() {
$('#tbSelectGoods').datagrid({//$("#id").控件('方法',{属性:属性值,事件:function(){}});
onClickRow: function (rowIndex) {
endEditing(editIndex);//调用方法endEditing();
if (editIndex != rowIndex) {//如果editIndex 不等 rowIndex
$('#tbSelectGoods').datagrid('beginEdit', rowIndex);//开启rowIndex行编辑
$('#tbSelectGoods').datagrid('endEdit', editIndex);//结束editIndex行编辑
getFaHuoKuCunDiDian(rowIndex);//调用方法绑定编辑行下拉框(发货部门地点)
getshouHuoDiDian(rowIndex);//调用方法绑定编辑行下拉框(收货部门地点)
endEditing2(rowIndex);//调用计算细数的方法
editIndex = rowIndex;//将rowIndex的值赋给editIndex
} else {
$('#tbSelectGoods').datagrid('endEdit', rowIndex);
$('#tbSelectGoods').datagrid('beginEdit', rowIndex);
getFaHuoKuCunDiDian(rowIndex);
getshouHuoDiDian(rowIndex);
endEditing2(rowIndex);
}
var packages = $("#tbSelectGoods").datagrid("getEditor", { index: rowIndex, field: 'Repertory_enter_packages' });//获取datagrid,中索引为‘index’,列为‘Repertory_enter_packages’的编辑器
var quantity = $("#tbSelectGoods").datagrid("getEditor", { index: rowIndex, field: 'Shipments_quantity' });
$(packages.target).numberbox({ min: 0 });//设值编辑器的最小值
$(quantity.target).numberbox({ min: 0 });
}
});
}
//进度框
function AddRunningDiv() {
$("<div class=\"datagrid-mask\"></div>").css({ display: "block", width: "100%", height: $(document).height() }).appendTo("body");
$("<div class=\"datagrid-mask-msg\"></div>").html("正在加载,请稍候...").appendTo("body").css({ display: "block", left: ($(document.body).outerWidth(true) - 190) / 2, top: ($(document).height() - 45) / 2 });
}
//生成记录编号
function createRecordNum() {
$.getJSON("/Allot/GetRecord_number", function (data) {
$('#Record_number').val(data);//生成记录编号显示出来
});
}
//绑定发货部门下拉树
function bingDingcombotree() {
$.getJSON("/Allot/GetConsignmentDepartmentAll", function (data) {
$('#cbo_ConsignmentDepartmentCode').combotree('loadData', data);//绑定部门下拉树
});
}
var n = null;
//点击新增
function tianJia() {
n = 0;
$('#wRecord_number').window('close');//关闭记录编号的窗体
if (intAllotId > 0 || $("#cbo_ConsignmentDepartmentCode").combotree("getValue") > 0) {
ui.confirm("确定放弃当前编辑?", function (k) {
if (k) {
refreshAfterDelete();//调用方法删除整个窗体数据
createRecordNum();//调用方法生成记录编号
bingDingcombotree();//调用方法生成下拉树
}
});
} else {
refreshAfterDelete();//调用方法删除整个窗体数据
createRecordNum();//调用方法生成记录编号
bingDingcombotree();//调用方法生成下拉树
}
}
//点击修改
function xiuGai() {
if (intAllotId > 0)
return false;
openjiLuBianHao();
}
//删除
function shanChu() {
if (intAllotId > 0) {
ui.confirm("确认删除?", function (g) {
if (g) {
$.getJSON("/Allot/DeleteAllotByAllotId?intAllotId=" + intAllotId, function (data2) {
if (data2 > 0) {//返回行数大于0则成功
ui.success("删除成功!", 1000, false);
refreshAfterDelete();//删除成功后调用方法清空控件
} else {
ui.error("删除失败!", 1000, false);
}
});
}
});
} else {
openjiLuBianHao();
}
}
//删除后的刷新
function refreshAfterDelete() {
parent.document.getElementById("labTips").innerHTML = "";
$('#tbSelectGoods').datagrid('loadData', { total: 0, rows: [] });
$('#Record_number').val("");
$('#cbo_ConsignmentDepartmentCode').combobox('clear');
$('#cbo_ConsignmentDepartmentCode').combobox('clear');
$('#cboReceivingDepartmentCode').combobox('clear');//清空combobox的值
$('#txtConsignmentDepartmentName').val("");
$('#txtReceivingDepartmentName').val("");
$('#txtShiFuHuoRenMC').val("");
$('#txtShiShouHuoRenMC').val("");
$('#txtBeiZhu').val("");
$('#cboMakeBills').combobox('clear');
$('#cboTransactor').combobox('clear');
$('#dtmMakeBills').datetimebox('clear');//清空datetimebox的值
$('#dtmExecute').datetimebox('clear');
intAllotId = 0;
Shipper_id = 0;
receiverId = 0;
n = null;
createRecordNum();
}
//放弃
function fanQi() {
if (intAllotId > 0) {
ui.confirm("确定放弃当前修改?", function (g) {
if (intAllotId > 0) {
refreshAfterXiuGai(intAllotId);
}
});
} else {
ui.confirm("确定放弃当前新增?", function (g) {
if (intAllotId > 0) {
refreshAfterDelete();
}
});
}
}
//审核
function reViewDiaoBoDan() {
if (intAllotId > 0) {
ui.confirm("确定审核?", function (g) {
if (g) {
parent.document.getElementById("labTips").innerHTML = "";//清空【提示信息】
$.getJSON("/Allot/ReViewAllot?intAllotId=" + intAllotId, function (data) {
if (data == "true") {
ui.success("审核成功!", 1000, false);
refreshAfterDelete();//审核成功后清空控件数据
} else if (data == "false") {
ui.error("未添加批次,审核失败!");
parent.document.getElementById("labTips").innerHTML = "<ol type='1'><li style='font-size:15px;margin-left:1px;'> 未添加批次</li></ol>";
} else {
ui.error("审核失败!<br/>详细原因请查看【提示信息】", 1000, false);//提示框
var tips = "";
var strs = data.split('+');//根据标识符‘+’拆分data。
for (var i = 0; i < strs.length - 1; i++) {
tips += "<li style='font-size:15px;margin-left:1px;'>" + strs[i] + "</li>";
}
parent.document.getElementById("labTips").innerHTML = "<ol type='1'>" + tips + "</ol>";//将动态构建的有序列的值赋给【提示信息】
}
});
}
});
} else {
ui.error("请先选择已保存的调拨单!", 1000, false);
}
}
//保存新增/修改
function saveDiaoBoDan() {
if (checkLost()) {//执行checkLost()方法,检查数据是否已输入完整
if (intAllotId > 0) {
ui.confirm('确认保存修改', function (g) {
if (g) {
var intShipperId = Shipper_id;
var intReceiverId = receiverId;
//获取选择行,然后获取选择行的索引,最后结束编辑,即结束编辑选择行
$("#tbSelectGoods").datagrid("endEdit", $("#tbSelectGoods").datagrid("getRowIndex", $("#tbSelectGoods").datagrid("getSelected")));
var delrow = $('#tbSelectGoods').datagrid('getChanges', 'deleted');//获取要移除的行
var insertrow = $('#tbSelectGoods').datagrid('getChanges', 'inserted');//获取要新增的行
var updaterow = $('#tbSelectGoods').datagrid('getChanges', 'updated');//获取要修改的行
for (var i = 0; i < delrow.length; i++) {
$.getJSON("/Allot/DeleteDiaoBoMingXiByDiaoBoMingXiId?intAllotDetailId=" + delrow[i].Allot_detail_id);//获取要删除的行的id,根据id删除明细
}
for (var j = 0; j < insertrow.length; j++) {//获取要新增的行新增
$.getJSON("/Allot/SaveAllotDetail?intAllotId=" + intAllotId + "&"
+ "intGoodsId=" + insertrow[j].Goods_id + "&"
+ "strShipmentsQuantity=" + insertrow[j].Repertory_enter_packages + "&"
+ "intRepertoryPlaceSendId=" + insertrow[j].Repertory_place_send_id + "&"
+ "intRepertoryPlaceHarvestId=" + insertrow[j].Repertory_place_harvest_id);
}
for (var k = 0; k < updaterow.length; k++) {//获取要修改的行
$.getJSON("/Allot/UpdateDiaoBoDanMingXi?intAllotDetailId=" + updaterow[k].Allot_detail_id + "&"
+ "strShipmentsQuantity=" + updaterow[k].Shipments_quantity + "&"
+ "intRepertoryPlaceSendId=" + updaterow[k].Repertory_place_send_id + "&"
+ "intRepertoryPlaceHarvestId=" + updaterow[k].Repertory_place_harvest_id);
}
$.getJSON("/Allot/UpdateDiaoBoDan?intdiaoBoId=" + intAllotId + "&"
+ "intConsignmentDepartmentId=" + $('#cbo_ConsignmentDepartmentCode').combotree('getValue') + "&"
+ "intReceivingDepartmentId=" + $('#cboReceivingDepartmentCode').combobox('getValue') + "&"
+ "intShipperId=" + intShipperId + "&"
+ "intReceiverId=" + intReceiverId + "&"
+ "strRemarks=" + $('#txtBeiZhu').val() + "&"
+ "intMakeBillsId=" + $('#cboMakeBills').combobox('getValue') + "&"
+ "intTransactorId=" + $('#cboTransactor').combobox('getValue') + "&"
+ "dtmMakeBillsTime=" + $('#dtmMakeBills').datetimebox('getValue') + "&" +
"dtmExecuteTime=" + $('#dtmExecute').datetimebox('getValue'), function (data1) {
if (data1 > 0) {//如果返回值大于0则成功
ui.success("修改成功!", 1000, false);
refreshAfterXiuGai(intAllotId);
} else {
ui.error("修改失败!", 1000, false);
}
});
}
});
} else if (intAllotId == 0) {
var consignmentDepartmentId = $('#cbo_ConsignmentDepartmentCode').combotree('getValue');
var receivingDepartmentId = $('#cboReceivingDepartmentCode').combobox('getValue');
ui.confirm("确认保存新增?", function (g) {
if (g) {
var diaoBoDanId = 0;
try {
var intShipperId1 = Shipper_id;
var intReceiverId1 = receiverId;
var recordNumber = $("#Record_number").val();
var remarks = $('#txtBeiZhu').val();
var makeBillsId = $('#cboMakeBills').combobox('getValue');
var makeBillsTime = $('#dtmMakeBills').datetimebox('getValue');
var transactorId = $('#cboTransactor').combobox('getValue');
var executeTime = $('#dtmExecute').datetimebox('getValue');
$.getJSON("/Allot/SaveAllot?strRecordNumber=" + recordNumber + "&"
+ "intConsignmentDepartment=" + consignmentDepartmentId + "&"
+ "intReceivingDepartment=" + receivingDepartmentId + "&"
+ "intShipperId=" + intShipperId1 + "&"
+ "intReceiverId=" + intReceiverId1 + "&"
+ "strRemarks=" + remarks + "&"
+ "intMakeBillsId=" + makeBillsId + "&"
+ "dtmMakeBills=" + makeBillsTime + "&"
+ "intTransactorId=" + transactorId + "&"
+ "dtmExecute=" + executeTime + "&", function (data) {
diaoBoDanId = data;
var value = $('#tbSelectGoods').datagrid('getRows');
for (var l = 0; l < value.length; l++) {
$.getJSON("/Allot/SaveAllotDetail?intAllotId=" + diaoBoDanId + "&"
+ "intGoodsId=" + value[l].Goods_id + "&"
+ "strShipmentsQuantity=" + value[l].Shipments_quantity + "&"
+ "intRepertoryPlaceSendId=" + value[l].Repertory_place_send_id + "&"
+ "intRepertoryPlaceHarvestId=" + value[l].Repertory_place_harvest_id + "&", function (data) {
if (data > 0) {
ui.success("新增成功!", 1000, false);
refreshAfterDelete();
} else {
return false;
}
});
}
});
} catch (e) {
ui.error("新增失败!", 1000, false);
}
}
});
} else {
ui.error("保存失败!", 1000, false);
}
}
}
//修改后的刷新
function refreshAfterXiuGai(diaoBoDanId) {
intAllotId = diaoBoDanId;
$('#tbSelectGoods').datagrid('loadData', { total: 0, rows: [] });//清空datagrid
intAllotId = intAllotId;
SelectAllotByAllotId(intAllotId);
SelectAllotDetailByAllotId(intAllotId);
}
//选择发货部门
function onConsignmentDepartmentCodeSelect(id) {
$.getJSON("/Allot/DepartmentNameSelect?intDeparId=" + id, function (value) {
$('#txtConsignmentDepartmentName').val(value.trim());//显示拼接好的部门名称
});
$.getJSON("/Allot/GetReceivingDepartmentById?intDepartmenId=" + id, function (data1) {
$('#cboReceivingDepartmentCode').combobox({ data: data1, valueField: 'id', textField: 'name', panelHeight: 150 });//绑定收货部门下拉框
if (harvestId > 0) {//如果收货部门id大于0,说明调拨单已是绑定
$('#cboReceivingDepartmentCode').combobox('setValue', harvestId);//给下拉框赋值
}
});
$.getJSON("/Allot/GetEmployeeByDepartId?intDepartmenId=" + id, function (data) {//根据部门id查询员工
$("#cboMakeBills").combobox({ panelHeight: '120', data: data, valueField: 'Employee_id', textField: 'Employee_name' });//绑定制单人
$("#cboTransactor").combobox({ panelHeight: '120', data: data, valueField: 'Employee_id', textField: 'Employee_name' });//绑定执行人
if (intMakeBills > 0) {//如果制单人id大于0就把制单人的id赋值给制单人下拉框
$("#cboMakeBills").combobox("setValue", intMakeBills);
}
if (intTransactor > 0) {
$("#cboTransactor").combobox("setValue", intTransactor);
}
});
}
//选择收货部门触发
function onShouHuoBuMenSelect(id) {
$.getJSON("/Allot/DepartmentNameSelect?intDeparId=" + id, function (value) {
$('#txtReceivingDepartmentName').val(value.trim());//拼接部门名称
});
}
//打开发货人选择窗体
function openwShipper() {
if ($('#txtConsignmentDepartmentName').val() != "") {//如果发货部门名称不为空
var intDepartmentFarId = $('#cbo_ConsignmentDepartmentCode').combotree('getValue');//获取发货部门id
var titles = $('#cbo_ConsignmentDepartmentCode').combotree('getText').trim() + "人员";//获取发货部门名称,拼接上”人员“
$('#tbShipper').datagrid({ url: '/Allot/DepartmentStaffSelectbyDepartmentId?intDepartmentFarId=' + intDepartmentFarId });//根据部门id查询属于这个部门的员工
$('#wShipper').window({ title: titles }).window('open');//设置发货部门的标题并打开
} else {
return false;//如果部门还没选择,打不开发货人窗体
}
}
// 打开收货人选择窗体
function openwReceiver() {
if ($('#txtReceivingDepartmentName').val() != "") {//如果收货部门名称不为空
var intDepartmentFarId = $('#cboReceivingDepartmentCode').combobox('getValue');//获取发货部门id
var titles = $('#cboReceivingDepartmentCode').combobox('getText').trim() + "人员";//获取收货部门名称,拼接上”人员“
$('#tbReceiver').datagrid({ url: '/Allot/DepartmentStaffSelectbyDepartmentId?intDepartmentFarId=' + intDepartmentFarId });//根据部门id查询属于这个部门的员工
$('#wReceiver').window({ title: titles }).window('open');
} else {
return false;//如果部门还没选择,打不开发货人窗体
}
}
//双击发货人选择窗体绑定发货人
var Shipper_id = 0;
function onDblClicktbShipper(index, data) {
Shipper_id = data.Employee_id;//将发货人id赋给全局变量
$('#txtShiFuHuoRenMC').val(data.Employee_name.trim());//给付货人文本框姓名
$('#wShipper').window('close');//关闭发货人窗体
}
//双击收货人选择窗体绑定收货人
var receiverId = 0;
function onDblClicktbReceiver(index, data) {
receiverId = data.Employee_id;//将收货人id赋给全局变量
$('#txtShiShouHuoRenMC').val(data.Employee_name.trim());//给收货人文本框姓名
$('#wReceiver').window('close');//关闭收货人窗体
}
//打开批量选择商品窗体
function openwAllGoods() {
$('#tbGoodsAll').datagrid('uncheckAll');//将批量选择窗体内的datagrid的checkbox全部设置为未选中
var values = $('#tbSelectGoods').datagrid('getRows');//获取调拨明细datagrid中的所有行
var str = [];
for (var j = 0; j < values.length; j++) {//遍历调拨明细datagrid中的所有行
str.push(values[j].Goods_id);//将行中的商品id添加到一个数组
}
$.getJSON("/Allot/GoodsAllSelect?id=" + 0 + "&" + "strarry=" + str, function (data) {//将拼接好的参数传过控制器
$('#tbGoodsAll').datagrid('loadData', data);//绑定返回的数据
});
$('#wAllGoods').window('open');
}
//打开批次窗体
function openwShangPinPiCi() {
var intAllotId1 = intAllotId;
$("#tbBatch1").datagrid("loadData", { total: 0, rows: [] });//清空datagrid(tbBatch1)
$("#tbBatch2").datagrid("loadData", { total: 0, rows: [] });//清空datagrid(tbBatch2)
if (intAllotId1 > 0) {//如果调拨单已绑定执行方法查询调拨明细
$.getJSON('/Allot/SelectAllotDetailByAllotId?intAllotId=' + intAllotId1, function (result) {
$.each(result, function (index, data) {
$('#tbBatch1').datagrid('appendRow', {//用each 的方遍历每一行新增到datagrid
Repertory_enter_packages: data['Repertory_enter_packages'],
Shipments_quantity: data['Shipments_quantity'],
Goods_id: data['Goods_id'],
Goods_code: data['Goods_code'],
Goods_name: data['Goods_name'],
Goods_bar_code: data['Goods_bar_code'],
Vender_bar_code_deny: data['Vender_bar_code_deny'],
Art_No: data['Art_No'],
Goods_abbreviation: data['Goods_abbreviation'],
Quality_content: data['Quality_content'],
Retail_unit_price: data['Retail_unit_price'],
Format_model: data['Format_model'],
Plncode_PLN: data['Plncode_PLN'],
Goods_tab: data['Goods_tab'],
Copy_record_deny: data['Copy_record_deny'],
Contract_number: data['Contract_number'],
Manufacturer_name: data['Manufacturer_name'],
Producing_area_name: data['Producing_area_name'],
Unit_of_measurement: data['Unit_of_measurement'],
Use_target: data['Use_target'],
Goods_colours: data['Goods_colours'],
Chinese: data['Chinese'],
Goods_classify: data['Goods_classify'],
Quality_guarantee_period: data['Quality_guarantee_period'],
Input_ratio: data['Input_ratio'],
Output_ratio: data['Output_ratio'],
Count_scale: data['Count_scale'],
Quality_grade: data['Quality_grade'],
Manage_season_name: data['Manage_season_name'],
Permit_decimal_deny: data['Permit_decimal_deny'],
Product_status: data['Product_status'],
Purchase_bid: data['Purchase_bid'],
Self_fix_goods_deny: data['Self_fix_goods_deny'],
Mini_fix_goods_deny: data['Mini_fix_goods_deny'],
Repertory_place_send_id: data['Repertory_place_send_id'],
Repertory_place_harvest_id: data['Repertory_place_harvest_id'],
Repertory_place_harvest: data['Repertory_place_harvest'],
Repertory_place_send: data['Repertory_place_send'],
Allot_detail_id: data['Allot_detail_id'],
Tax_inclusive_price: data['Tax_inclusive_price']
});
});
});
$('#wBatch').window('open');
} else {
return false;//如调拨单id未选择,无法打开批次窗体
}
}
//删除调拨明细
function deleteSelect(index, data) {
return "<img class='myimg' οnclick='javaScript:deletess(" + data.Goods_id + ");' src='../../Content/image/delete.png' />";//返回一张图片
}
//移除datagrid的行
function deletess(id) {
$('#tbSelectGoods').datagrid('deleteRow', $('#tbSelectGoods').datagrid('getRowIndex', id));//根据选择的明细id找到所在行的索引,然后根据索引移除该行
}
//模糊查询全部商品
function moHiuChaXun(strMoHu) {
$('#tbGoodsAll').datagrid({ url: '/Allot/BlurSelect?strMoHu=' + strMoHu });
}
//获取商品信息by商品id
function getChangPin() {
var rows = $("#tbGoodsAll").datagrid("getChecked");//获取批量选择中选择的行
for (var i = 0; i < rows.length; i++) {
$.getJSON("/Allot/GoodsAllSelect1?id=" + rows[i].Goods_id,//根据商品id查询商品叠加到调拨明细
function (result) {
$.each(result, function (index, data) {
$('#tbSelectGoods').datagrid('appendRow', {
Goods_id: data['Goods_id'],
Goods_code: data['Goods_code'],
Goods_name: data['Goods_name'],
Goods_bar_code: data['Goods_bar_code'],
Vender_bar_code_deny: data['Vender_bar_code_deny'],
Art_No: data['Art_No'],
Goods_abbreviation: data['Goods_abbreviation'],
Quality_content: data['Quality_content'],
Retail_unit_price: data['Retail_unit_price'],
Format_model: data['Format_model'],
Plncode_PLN: data['Plncode_PLN'],
Goods_tab: data['Goods_tab'],
Copy_record_deny: data['Copy_record_deny'],
Contract_number: data['Contract_number'],
Manufacturer_name: data['Manufacturer_name'],
Producing_area_name: data['Producing_area_name'],
Unit_of_measurement: data['Unit_of_measurement'],
Use_target: data['Use_target'],
Goods_colours: data['Goods_colours'],
Chinese: data['Chinese'],
Goods_classify: data['Goods_classify'],
Quality_guarantee_period: data['Quality_guarantee_period'],
Input_ratio: data['Input_ratio'],
Output_ratio: data['Output_ratio'],
Count_scale: data['Count_scale'],
Quality_grade: data['Quality_grade'],
Manage_season_name: data['Manage_season_name'],
Permit_decimal_deny: data['Permit_decimal_deny'],
Product_status: data['Product_status'],
Purchase_bid: data['Purchase_bid'],
Self_fix_goods_deny: data['Self_fix_goods_deny'],
Mini_fix_goods_deny: data['Mini_fix_goods_deny']
});
});
}
);
$('#wAllGoods').window('close');
}
}
function endEditing(editIndex) {
if (editIndex == undefined) {
return true;
}
if ($('#tbSelectGoods').datagrid('validateRow', editIndex)) {//验证是否存在该行
try {
var faHuoKuCunid = $('#tbSelectGoods').datagrid('getEditor', { index: editIndex, field: 'Repertory_place_send_id' }).target;//获取索引为editIndex,列为Repertory_place_send_id的单元的target(目标)
var faHuoKuCunmc = $(faHuoKuCunid).combobox('getText');//获取目标编辑器的值
$('#tbSelectGoods').datagrid('getRows')[editIndex]['Repertory_place_send'] = faHuoKuCunmc;
var shouHuoKuCunid = $('#tbSelectGoods').datagrid('getEditor', { index: editIndex, field: 'Repertory_place_harvest_id' }).target;
var shouHuoKuCunmc = $(shouHuoKuCunid).combobox('getText');
$('#tbSelectGoods').datagrid('getRows')[editIndex]['Repertory_place_harvest'] = shouHuoKuCunmc;
} catch (e) {
$('#tbSelectGoods').datagrid('endEdit', editIndex); //结束编辑
return true;
}
} else {
return false;
}
return true;
}//结束编辑方法
//计算细数
function endEditing2(rowIndex) {
var editors = $('#tbSelectGoods').datagrid('getEditors', rowIndex);//获取索引等于rowIndex的行的所有编辑器
var row = $('#tbSelectGoods').datagrid('getRows');//获取所有行
var jianShu = editors[0];//获取第一个编辑器
var caiGouJia = parseFloat(row[rowIndex].Purchase_bid);//获取索引等于rowIndex的行中列名为Purchase_bid的单元格的值,并将其转换为浮点型
var jinXiangShuilv = parseFloat(row[rowIndex].Input_ratio);
var netAmount = editors[3];//获取第四个编辑器
var hanShuiJia = editors[4];
var baoZhuanHanliang = row[rowIndex].Quality_content;
jianShu.target.bind('change', function () {//jianShu,这个编辑器改变时执行
$(hanShuiJia.target).numberbox('setValue', ((jinXiangShuilv / 100 + 1) * caiGouJia));//计算出数设置给编辑器中的numberbox
$(netAmount.target).numberbox('setValue', (jianShu.target.val() * baoZhuanHanliang));//目标编辑器的类型由界面设置的editor决定;
});
}
//日期格式化
function myformatter(date) {//参数为选择的日期
var y = date.getFullYear();//获取年
var m = date.getMonth() + 1;//获取月
var d = date.getDate();//获取日
var h = date.getHours();//获取小时
var min = date.getMinutes();//获取分钟
var s = date.getSeconds();//获取秒
return y + "-" + f(m) + "-" + f(d) + " " + f(h) + ":" + f(min) + ":" + f(s);
}
function f(k) {//如果传入的数小于10则在前面加‘0’
return (k < 10 ? ('0' + k) : k);
}
//点击记录编号打开记录编号查询窗口
function openjiLuBianHao() {
n = 1;
parent.document.getElementById("labTips").innerHTML = "";//清空‘提示信息’框
$('#wRecord_number').window('open');
$('#tbJiLuBianHao').datagrid({ url: '/Allot/GetAllRecordNumberOfAllot?strMoHu=', loadMsg: '正在加载,请稍侯...' });//加载调拨单
}
//双击记录编号查询窗口datagrid
var intAllotId = 0;
var harvestId = 0;
function onDblBanHaoRow(rowIndex, rowData) {
refreshAfterDelete();//执行绑定数据前先清空原来控件原有的数据
intAllotId = rowData.Allot_id;//把调拨id赋给全局变量
harvestId = rowData.Receiving_department_id;
SelectAllotByAllotId(rowData.Allot_id);
SelectAllotDetailByAllotId(rowData.Allot_id);
$('#wRecord_number').window('close');
}
var intMakeBills = 0;
var intTransactor = 0;
//根据调拨id查找调拨单
function SelectAllotByAllotId(intAllotId) {
$.getJSON("/Allot/SelectAllotByAllotId?intAllotId=" + intAllotId, function (data) {
$('#cbo_ConsignmentDepartmentCode').combotree('setValue', data[0].Consignment_department_id);//给发货部门设置id
$('#txtShiFuHuoRenMC').val(data[0].Shipper.trim());//赋值给发货人
$('#txtShiShouHuoRenMC').val(data[0].Receiver.trim());//赋值给收货人
Shipper_id = data[0].Shipper_id;//发货人id
receiverId = data[0].Receiver_id;//收货人id
$('#txtBeiZhu').val(data[0].Remarks);//绑定备注
intMakeBills = data[0].Make_bills_id;//制单人id
$('#dtmMakeBills').datetimebox('setValue', data[0].Make_bills_time);//设置制单时间
intTransactor = data[0].Transactor_id;//执行人id
$('#dtmExecute').datetimebox('setValue', data[0].Execute_time);//设置执行时间
$('#Record_number').val(data[0].Record_number);//记录编号
});
}
//根据调拨id查找调拨明细
function SelectAllotDetailByAllotId(intAllotId) {
$.getJSON('/Allot/SelectAllotDetailByAllotId?intAllotId=' + intAllotId, function (result) {//根据调拨单id查询调拨明细
$.each(result, function (index, data) {
$('#tbSelectGoods').datagrid('appendRow', {
Repertory_enter_packages: data['Repertory_enter_packages'],
Shipments_quantity: data['Shipments_quantity'],
Goods_id: data['Goods_id'],
Goods_code: data['Goods_code'],
Goods_name: data['Goods_name'],
Goods_bar_code: data['Goods_bar_code'],
Vender_bar_code_deny: data['Vender_bar_code_deny'],
Art_No: data['Art_No'],
Goods_abbreviation: data['Goods_abbreviation'],
Quality_content: data['Quality_content'],
Retail_unit_price: data['Retail_unit_price'],
Format_model: data['Format_model'],
Plncode_PLN: data['Plncode_PLN'],
Goods_tab: data['Goods_tab'],
Copy_record_deny: data['Copy_record_deny'],
Contract_number: data['Contract_number'],
Manufacturer_name: data['Manufacturer_name'],
Producing_area_name: data['Producing_area_name'],
Unit_of_measurement: data['Unit_of_measurement'],
Use_target: data['Use_target'],
Goods_colours: data['Goods_colours'],
Chinese: data['Chinese'],
Goods_classify: data['Goods_classify'],
Quality_guarantee_period: data['Quality_guarantee_period'],
Input_ratio: data['Input_ratio'],
Output_ratio: data['Output_ratio'],
Count_scale: data['Count_scale'],
Quality_grade: data['Quality_grade'],
Manage_season_name: data['Manage_season_name'],
Permit_decimal_deny: data['Permit_decimal_deny'],
Product_status: data['Product_status'],
Purchase_bid: data['Purchase_bid'],
Self_fix_goods_deny: data['Self_fix_goods_deny'],
Mini_fix_goods_deny: data['Mini_fix_goods_deny'],
Repertory_place_send_id: data['Repertory_place_send_id'],
Repertory_place_harvest_id: data['Repertory_place_harvest_id'],
Repertory_place_harvest: data['Repertory_place_harvest'],
Repertory_place_send: data['Repertory_place_send'],
Allot_detail_id: data['Allot_detail_id'],
Tax_inclusive_price: data['Tax_inclusive_price']
});
});
$('#tbSelectGoods').datagrid('acceptChanges');//绑定上数后设置一个记录改变的方法,用来记录表之后的修改,新增,删除
});
}
//验漏,用于修改或新增的保存之前的检查错漏
function checkLost() {
var temp = true;//获取选择的行,然后根据获取的行获取索引,最后根据获取到的索引结束该行的编辑。
$("#tbSelectGoods").datagrid("endEdit", $("#tbSelectGoods").datagrid("getRowIndex", $("#tbSelectGoods").datagrid("getSelected")));
if ($('#Record_number').val() == '' || $('#cbo_ConsignmentDepartmentCode').combotree('getValue') == '' ||
$('#cboReceivingDepartmentCode').combobox('getValue') == '' || $('#txtShiFuHuoRenMC').val() == '' ||
$('#txtShiShouHuoRenMC').val() == '' || $('#cboMakeBills').combobox('getText') == '' ||
$('#cboTransactor').combobox('getText') == '' || $('#dtmMakeBills').datetimebox('getText') == '' ||
$('#dtmExecute').datetimebox('getText') == '') {//检查必填项是否都有数据
if ($('#Record_number').val() == '') {//如果记录编号为空,记录编号的边框设置为红色
$('#Record_number').css('border-color', 'red');
} else {
$('#Record_number').css('border-color', '#ccc');
}
if ($('#txtShiFuHuoRenMC').val() == '') {
$('#txtShiFuHuoRenMC').css('border-color', 'red');
} else {
$('#txtShiFuHuoRenMC').css('border-color', '#ccc');
}
if ($('#txtShiShouHuoRenMC').val() == '') {
$('#txtShiShouHuoRenMC').css('border-color', 'red');
} else {
$('#txtShiShouHuoRenMC').css('border-color', '#ccc');
}
$("td input").mouseover();//设置鼠标经过,如果必选项没数据会提示”必选项“
ui.error("数据不完整,请检查!");
temp = false;
} else {
$('#txtShiShouHuoRenMC').css('border-color', '#ccc');
$('#txtShiFuHuoRenMC').css('border-color', '#ccc');
$('#Record_number').css('border-color', '#ccc');
var rows = $('#tbSelectGoods').datagrid('getRows');//获取所有调拨明细
for (var i = 0; i < rows.length; i++) {
$('#tbSelectGoods').datagrid('beginEdit', i).datagrid('endEdit', i);//开启每行再结束每行的编辑
if (rows[i].Repertory_enter_packages == '' || rows[i].Repertory_place_send_id == '' || rows[i].Repertory_place_harvest_id == '') {//如果有某一行这些必填项为空,就警告表格数据不完整
temp = false;
ui.alert("表格数据不完整", 800, false);
$('#tbSelectGoods').datagrid('selectRow', i);//并选择该行
break;//并且停止该循环
}
}
}
return temp;
}
//模糊查询调拨单
function selectAllotByBlur(strBlur) {
$('#tbJiLuBianHao').datagrid({ url: '/Allot/GetAllRecordNumberOfAllot?strMoHu=' + strBlur });
}
//根据发货部门绑定datagrid中combobox
function getFaHuoKuCunDiDian(rowIndex) {
var target = $('#tbSelectGoods').datagrid('getEditor', { index: rowIndex, field: 'Repertory_place_send_id' }).target;//获取datagrid中的发货部库存地点下拉框目标,
var url = '/Allot/RepertoryPlaceSendSelect?buMeId=' + $('#cbo_ConsignmentDepartmentCode').combotree('getValue');//拼接查询发货部门的库地点的url
target.combobox('reload', url);
}
//根据收货部门绑定datagrid中combobox
function getshouHuoDiDian(rowIndex) {
var target = $('#tbSelectGoods').datagrid('getEditor', { index: rowIndex, field: 'Repertory_place_harvest_id' }).target;//获取datagrid中的收货部库存地点下拉框目标,
var url = '/Allot/RepertoryPlaceHarvestSelect?buMeId=' + $('#cboReceivingDepartmentCode').combobox('getValue');//拼接查询收货部门的库地点的url
target.combobox('reload', url);
}
//自动生成批次
var aa = 0;
function ziDongShengChenPiCi() {
var param = "";
parent.document.getElementById("labTips").innerHTML = "";
var rows = $("#tbBatch1").datagrid("getRows");//获取所有行,拼接调拨明细id,商品id,发货地点,发货数量,商品名称。
for (var i = 0; i < rows.length; i++) {
param += rows[i].Allot_detail_id + ",";
param += rows[i].Goods_id + ",";
param += rows[i].Repertory_place_send_id + ",";
param += rows[i].Shipments_quantity + ",";
param += rows[i].Goods_name + ".";
}
aa = rows[0].Allot_detail_id;
$.getJSON("/Allot/AutoCreatePatch?pm=" + param,
function (data) {
var strs = data.split('+');//将返回的数据根据‘+’来拆分
if (strs[0] != "") {//判断拆分出来的数组中的第一项是否不为空
var tips = [];//声明一个数组
if (strs[0] == "true") {//如果拆分出来的数组的第一项为”true“
for (var j = 1; j < strs.length - 1; j++) {//拼接返回的字符串
tips.push("<li style='font-size:15px;margin-left:1px;'>" + strs[j] + "</li>");
}
parent.document.getElementById("labTips").innerHTML = "<ol type='1'>" + tips + "</ol>";//通过DOM向”提示信息“设置值
ui.alert("部分自动生成批次失败!<br/>详情请留意【提示信息】", 1000, false);
} else {
for (var k = 1; k < strs.length - 1; k++) {
tips.push("<li style='font-size:15px;margin-left:1px;'>" + strs[k] + "</li>");
}
parent.document.getElementById("labTips").innerHTML = "<ol type='1'>" + tips + "</ol>";
ui.alert("自动生成批次失败!<br/>详情请留意【提示信息】", 1000, false);
}
} else {
ui.success("全部商品自动分批成功!", 1000, false);
getGoodsPatch(aa);
}
});
}
//选择商品明细绑定批次
function onGoodsSelect(index, data) {
getGoodsPatch(data.Allot_detail_id);
}
//获取商品批次
function getGoodsPatch(intDetailId) {
$("#tbBatch2").datagrid({ url: "/Allot/SelectPatchMessage?intDetailId=" + intDetailId });
}
//点击修改配货批次
function updateBatch() {
var row = $("#tbBatch1").datagrid("getSelected");//获取选择的批次
$("#tbBatch2").datagrid({
url: "/Allot/GetallEnterPatch?placeId=" + row.Repertory_place_send_id//根据库存地点,商品id,调拨明细id查找该商品在该库存地点的所有进货批次
+ "&goodId=" + row.Goods_id + "&detailId=" + row.Allot_detail_id
});
$('#tbBatch2').datagrid({ onClickRow: ontbBatchClick });//给datagrid设置个”onClickRow“的属性
}
//单击配货批次行
var oldIndex = 0;
function ontbBatchClick(rowIndex) {
if (oldIndex != rowIndex) {
$("#tbBatch2").datagrid("endEdit", oldIndex).datagrid("beginEdit", rowIndex);//结束索引为oldindex的行,在开启rowIndex行
compute(rowIndex);//调用计算方法计算
oldIndex = rowIndex;
} else {
$("#tbBatch2").datagrid("endEdit", rowIndex).datagrid("beginEdit", rowIndex);//如果点击的是同一行,获取的索引是相同的,就把这行先结束编辑再结束编辑
}
}
function compute(rowIndex) {
var shipmentsQuantity = $("#tbBatch1").datagrid("getSelected").Shipments_quantity;//获取(tbBatch1)选择行的配货数量
var rows = $("#tbBatch2").datagrid("getRows");//获取(tbBatch2)的所有行
var other = 0;
for (var i = 0; i < rows.length; i++) {//遍历(tbBatch2)的行
if (i == rowIndex) {//如果遍历到rowIndex行时
continue;//跳过当次遍历
}
other += parseInt(rows[i].Delivery_quantity);//累加其他行的配货数量
}
$("#tbBatch2").datagrid("updateRow", { index: rowIndex, row: { Delivery_quantity: parseInt(shipmentsQuantity - other) } });//将总配货数减去其他行数的总和,得出的差给当前行
}
//修改批次
function updatePatch() {
var rows = $("#tbBatch2").datagrid("getRows");//获取所有行
var count = 0;
for (var i = 0; i < rows.length; i++) {//遍历所有行修改
$.getJSON("/Allot/UpdatePatch?intPatchId=" + rows[i].Patch_id +
"&Quantity=" + rows[i].Delivery_quantity);
count++;
}
if (count == rows.length) {
ui.success("修改成功!", 1000, false);
} else {
ui.error("修改失败!", 1000, false);
}
}
var k = 0;
//onSelect制单人
function selMb() {
if (n == 1) {
if (k > 0) {
$("#dtmMakeBills").datetimebox("setValue", getDateTime());//给制单时间设置当前时间
} else {
k++;
}
} else {
$("#dtmMakeBills").datetimebox("setValue", getDateTime());
}
}
var j = 0;
//onSelect执行人
function selTs() {
if (n == 1) {
if (j > 0) {
$("#dtmExecute").datetimebox("setValue", getDateTime());//给执行时间设置当前时间
} else {
j++;
}
} else {
$("#dtmExecute").datetimebox("setValue", getDateTime());
}
}
//获取系统时间
function getDateTime() {
var date = new Date();//获取当前系统时间
var y = date.getFullYear();
var m = date.getMonth() + 1;
var d = date.getDate();
var h = date.getHours();
var min = date.getMinutes();
var s = date.getSeconds();
var time = y + "-" + f(m) + "-" + f(d) + " " + f(h) + ":" + f(min) + ":" + f(s);
return time;//返回时间
}
2.3、 商品调拨查询HTML代码:
<!DOCTYPE html>
<html>
<head>
<meta content="text/javascript;charset=utf-8" />
<title>SelectAllot</title>
<link href="../../Content/jquery-easyui-1.3.3/themes/icon.css" rel="stylesheet" />
<link href="../../Content/jquery-easyui-1.3.3/demo/demo.css" rel="stylesheet" />
<link href="../../Content/jquery-easyui-1.3.3/themes/default/easyui.css" rel="stylesheet" />
<link href="../../Content/MyCSS/SelectAllot.css" rel="stylesheet" />
<script src="../../Content/jquery-easyui-1.3.3/jquery.min.js"></script>
<script src="../../Content/jquery-easyui-1.3.3/jquery.easyui.min.js"></script>
<script src="../../Content/jquery-easyui-1.3.3/easyloader.js"></script>
<script src="../../Scripts/MyScript/SelectAllot.js"></script>
<style type="text/css">
.Abutton { width: 65px; }
.easyui-datebox, .easyui-combobox, .easyui-combotree { width: 140px; }
</style>
</head>
<body οnlοad="AddRunningDiv();" style="text-align: center">
<div style="display: none">
<div class="easyui-window" id="wBuMenTiaoZheng" title="" style="display: none; text-align: center; border-spacing: 0; padding: 0; border: none; overflow: hidden;" data-options="fit:true,collapsible:false,minimizable:false,
maximizable:false,closable:false,draggable:false,resizable:false,shadow:false,modal:true,center:true">
<div style="padding: 0; background: #e0ffff; color: #4169e1; font-size: 20px; text-align: center; margin: 0 0 auto;">
<span style="float: right">
<img οnclick="parent.allot();" id="dimg" src="../../Content/image/delete.png" /></span>查询调拨单
</div>
<fieldset style="border: 1px #4169e1 solid; height: 60px; margin: 0 0 0 0; text-align: left; padding-left: 5px; overflow: hidden">
<legend>不定项查询条件</legend>
<div style="float: right; width: 140px; height: 55px; text-align: center">
<span style="margin: auto">审核否<input type="checkbox" id="check" checked="checked" /></span><br />
<span style="margin: 3px"></span>
<button class="Abutton" οnclick="selectAllot();">查询</button>
<button class="Abutton" οnclick="refurbish();">刷新</button>
</div>
<table class="table1" style="border: none; margin: 0 0 0 0; padding: 0; width: auto;">
<tr>
<td style="width: 60px;">记录编号:</td>
<td style="width: 130px;">
<input type="text" style="width: 140px;" id="txtRecordNumber" />
</td>
<td style="width: 60px;">发货部门:</td>
<td style="width: 130px;">
<input class="easyui-combotree" id="cobtrConsignmentDepartment" data-options="onChange:onConsignmentDepartmentCodeSelect" /></td>
<td style="width: 60px;">收货部门:</td>
<td style="width: 130px;">
<input class="easyui-combobox" id="cboReceivingDepartment" /></td>
</tr>
<tr>
<td>开始时间:
</td>
<td>
<input class="easyui-datebox" id="dtmStart" data-options="formatter:myformatter" />
</td>
<td>结束时间:</td>
<td>
<input class="easyui-datebox" id="dtmEnd" data-options="formatter:myformatter" />
</td>
<td>备 注:</td>
<td>
<input type="text" id="txtRemarks" /></td>
</tr>
</table>
</fieldset>
<div style="width: 500px; height: 490px; float: right; border: 1px solid #c0c0c0; clear: left">
<div style="width: 500px; height: 250px; float: right; border: 1px solid #c0c0c0; clear: left">
<table title="调拨明细" id="tbAllotDetail" class="easyui-datagrid" style="margin: 0 10px 0 0; height: 265px;" data-options="rownumbers:true,fit:true,singleSelect:true,
idField:'Goods_id',onClickRow:getPatch">
<thead>
<tr>
<th data-options="field:'Goods_name',width:80,align:'center'">商品名称</th>
<th data-options="field:'Goods_code',width:80,align:'center'">商品代码</th>
<th data-options="field:'Shipments_quantity',width:60,align:'center'">发货件数</th>
<th data-options="field:'Amount',width:65,align:'center'">发货细数</th>
<th data-options="field:'RepertoryPlaceSend',align:'center',width:90">发货库存地点</th>
<th data-options="field:'RepertoryPlaceHarvest',align:'center',width:90">收货库存地点</th>
<th data-options="field:'Goods_bar_code',width:80,align:'center'">商品条码</th>
<th data-options="field:'InputTax',width:80,align:'center'">含税进价</th>
<th data-options="field:'Goods_abbreviation',width:50,align:'center'">商品简称</th>
<th data-options="field:'Quality_content',width:55 ,align:'center'">包装含量</th>
<th data-options="field:'Art_No',width:80,align:'center'">货号</th>
<th data-options="field:'Retail_unit_price',width:80,align:'center'">零售单价</th>
<th data-options="field:'Vender_bar_code_deny',width:80,align:'center'">厂家条码否</th>
<th data-options="field:'Format_model',width:80,align:'center'">规格型号</th>
<th data-options="field:'Plncode_PLN',width:80,align:'center'">PLN码</th>
<th data-options="field:'Goods_Tab',width:80,align:'center'">商品标记</th>
<th data-options="field:'Copy_record_deny',width:80,align:'center'">复制记录否</th>
<th data-options="field:'Contract_number',width:80,align:'center'">合同号</th>
<th data-options="field:'Manufacturer_name',width:80,align:'center'">生产厂家</th>
<th data-options="field:'Producing_area_name',width:80,align:'center'">产地</th>
<th data-options="field:'Unit_of_measurement',width:80,align:'center'">计量单位</th>
<th data-options="field:'Use_target',width:60,align:'center'">使用对象</th>
<th data-options="field:'Goods_colours',width:80,align:'center'">商品花色</th>
<th data-options="field:'Chinese',width:80,align:'center'">供应单位</th>
<th data-options="field:'Goods_classify',width:80,align:'center'">商品分类</th>
<th data-options="field:'Quality_guarantee_period',width:80,align:'center'">保质期</th>
<th data-options="field:'Input_ratio',width:50,align:'center'">进项税率</th>
<th data-options="field:'Output_ratio',width:50,align:'center'">销项税率</th>
<th data-options="field:'Count_scale',width:50,align:'center'">统计比例</th>
<th data-options="field:'Quality_grade',width:50,align:'center'">质量等级</th>
<th data-options="field:'Manage_season_name',width:50,align:'center'">经营季节</th>
<th data-options="field:'Permit_decimal_deny',width:50,align:'center'">允许销售否</th>
<th data-options="field:'Product_status',width:80,align:'center'">产品状态</th>
<th data-options="field:'Registrant',width:80,align:'center'">登记人</th>
<th data-options="field:'RegisterTime',width:120,align:'center'">登记时间</th>
<th data-options="field:'Review',width:80,align:'center'">审核人</th>
<th data-options="field:'ReviewTime',width:120,align:'center'">审核时间</th>
<th data-options="field:'Update_person',width:80,align:'center'">更新人</th>
<th data-options="field:'UpdateTime',width:120,align:'center'">更新时间</th>
<th data-options="field:'Purchase_bid',width:80,align:'center'">采购进价</th>
<th data-options="field:'Self_fix_goods_deny',width:60,align:'center'">自订货否</th>
<th data-options="field:'Mini_fix_goods_deny',width:70,align:'center'">最小订货数</th>
<th data-options="field:'Allot_detail_id',hidden:true"></th>
</tr>
</thead>
</table>
</div>
<div style="height: 237px; border: 1px solid white">
<table class="easyui-datagrid" title="配送批次" id="tbBatch2" style="width: 700px; border: none" data-options="rownumbers:true,fit:true,singleSelect:true,
idField:'Patch_id'">
<thead>
<tr>
<th data-options="field:'Patch_number',width:150,align:'center'">批次号</th>
<th data-options="field:'Delivery_quantity',editor:{type:'numberbox'},width:120,align:'center'">配货数量</th>
<th data-options="field:'Stock_quantity',width:120,align:'center'">结存数量</th>
</tr>
</thead>
</table>
</div>
</div>
<div style="height: 490px; width: 350px; border: 1px solid #c0c0c0;">
<table title="调拨单" id="tbAllot" class="easyui-datagrid" style="width: 350px; height: 480px; border: 1px solid black" border="1" data-options="rownumbers:true,fit:true,singleSelect:true,idField:'Allot_id',
onClickRow:onClicktbAllot,pagination:'true'">
<thead>
<tr>
<th data-options="field:'Record_number',width:110,align:'center'">记录编号</th>
<th data-options="field:'ConsignmentDepartment',width:90,align:'center'">发货部门</th>
<th data-options="field:'ReceivingDepartment',width:90,align:'center'">收货部门</th>
<th data-options="field:'Shipper',width:90,align:'center'">发货人</th>
<th data-options="field:'Receiver',width:90,align:'center'">收货人</th>
<th data-options="field:'Make_bills',width:90,align:'center'">制单人</th>
<th data-options="field:'Make_bills_time',width:130,align:'center'">制单时间</th>
<th data-options="field:'Transactor',width:90,align:'center'">执行人</th>
<th data-options="field:'Execute_time',width:130,align:'center'">执行时间</th>
<th data-options="field:'Remarks',width:90,align:'center'">备注</th>
</tr>
</thead>
</table>
</div>
</div>
</div>
</body>
</html>
2.4、商品调拨查询JavaScript代码:
$(document).ready(function () {
$("#wBuMenTiaoZheng").show(500);
$.getJSON("/Allot/GetConsignmentDepartmentAll", function (data) {
$('#cobtrConsignmentDepartment').combotree('loadData', data);
setTimeout(" selectAllot();", 100);
});
});
//加载框
function AddRunningDiv() {
$("<div class=\"datagrid-mask\"></div>").css({ display: "block", width: "100%", height: $(document).height() }).appendTo("body");
$("<div class=\"datagrid-mask-msg\"></div>").html("正在加载,请稍候...").appendTo("body").css({ display: "block", left: ($(document.body).outerWidth(true) - 190) / 2, top: ($(document).height() - 45) / 2 });
}
//日期格式化
function myformatter(date) {//参数为选择的日期
var y = date.getFullYear();//获取年
var m = date.getMonth() + 1;//获取月
var d = date.getDate();//获取日
var h = date.getHours();//获取小时
var min = date.getMinutes();//获取分钟
var s = date.getSeconds();//获取秒
return y + "-" + f(m) + "-" + f(d) + " " + f(h) + ":" + f(min) + ":" + f(s);
}
function f(k) {//如果传入的数小于10则在前面加‘0’
return (k < 10 ? ('0' + k) : k);
}
//选择发货部门,绑定收货部门
function onConsignmentDepartmentCodeSelect(id) {
$.getJSON("/Allot/GetReceivingDepartmentAllByDepartmentId?intDepartmentFarId=" + id, function (data) {
if (data[0].farid >= 0) {
$.getJSON("/Allot/GetReceivingDepartmentByFarId?intDepartmentFarId=" + data[0].farid, function (data1) {
$('#cboReceivingDepartment').combobox({ data: data1, valueField: 'id', textField: 'MC' });
});
}
});
}
//查询调拨单
function selectAllot() {
var strRecordNumber = $('#txtRecordNumber').val();
var cbotrConsignmentDepartment = $('#cobtrConsignmentDepartment').combotree('getValue');
var cboReceivingDepartment = $('#cboReceivingDepartment').combobox('getValue');
var dtmStart = $('#dtmStart').datebox('getValue');
var dtmEnd = $('#dtmEnd').datebox('getValue');
var txtRemarks = $("#txtRemarks").val();
var chkCheck = $("#check").prop("checked");
$.getJSON("/Allot/SelectAllAllotOfReview?" +
"strRecordNumber=" + strRecordNumber + "&" +
"intConsignmentDepartment=" + cbotrConsignmentDepartment + "&" +
"intReceivingDepartment=" + cboReceivingDepartment + "&" +
"dtmStart=" + dtmStart + "&" +
"dtmEnd=" + dtmEnd + "&" +
"strRemarks=" + txtRemarks + "&" +
"blnCheck=" + chkCheck, function (data) {
if (data != "") {//如果返回值不为空,就加载,否则清空原来的数据
$("#tbAllot").datagrid({ loadFilter: pagerFilter }).datagrid('loadData', data);
} else {
$("#tbAllot").datagrid('loadData', { total: 0, rows: [] });
$("#tbAllotDetail").datagrid('loadData', { total: 0, rows: [] });
$("#tbBatch2").datagrid('loadData', { total: 0, rows: [] });
$("#ltxtRecordNumber").val("");
$("#ltxtConsignmentDepartment").val("");
$("#ltxtReceivingDepartment").val("");
$("#ltxtShipper").val("");
$("#ltxtReceiver").val("");
$("#ltxtMake_bills").val("");
$("#ltxtMake_bills_time").val("");
$("#ltxtTransactor").val("");
$("#ltxtExecute_time").val("");
$("#ltxtRemarks").val("");
}
});
function pagerFilter(data) {//JavaScript,datagrid分页
if (typeof data.length == 'number' && typeof data.splice == 'function') { // is array
data = {
total: data.length,
rows: data
}
}
var dg = $(this);
var opts = dg.datagrid('options');
var pager = dg.datagrid('getPager');
pager.pagination({
onSelectPage: function (pageNum, pageSize) {
opts.pageNumber = pageNum;
opts.pageSize = pageSize;
pager.pagination('refresh', {
pageNumber: pageNum,
pageSize: pageSize
});
dg.datagrid('loadData', data);
}
});
if (!data.originalRows) {
data.originalRows = (data.rows);
}
var start = (opts.pageNumber - 1) * parseInt(opts.pageSize);
var end = start + parseInt(opts.pageSize);
data.rows = (data.originalRows.slice(start, end));
return data;
}
}
//点击调拨单
function onClicktbAllot(rowIndex, rowDate) {
var intAllotId = rowDate.Allot_id;
$.getJSON("/Allot/GetAllot?intAllotId=" + intAllotId, function (data) {
$('#ltxtRecordNumber').val(data[0].Record_number);
$('#ltxtConsignmentDepartment').val(data[0].ConsignmentDepartment);
$('#ltxtShipper').val(data[0].Shipper);
$('#ltxtReceivingDepartment').val(data[0].ReceivingDepartment);
$('#ltxtReceiver').val(data[0].Receiver);
$('#ltxtMake_bills').val(data[0].Make_bills);
$('#ltxtMake_bills_time').val(data[0].Make_bills_time);
$('#ltxtTransactor').val(data[0].Transactor);
$('#ltxtExecute_time').val(data[0].Execute_time);
$('#ltxtRemarks').val(data[0].Remarks);
});
$("#tbAllotDetail").datagrid({ url: "/Allot/GetAllotDetail?intAllotId=" + intAllotId, loadMsg: '正在加载,请稍侯...' });
}
//清空控件数据
function refurbish() {
$("#tbAllotDetail").datagrid('loadData', { total: 0, rows: [] });
$("#tbAllot").datagrid('loadData', { total: 0, rows: [] });
$("#tbBatch2").datagrid('loadData', { total: 0, rows: [] });
$("#ltxtRecordNumber").val("");
$("#ltxtConsignmentDepartment").val("");
$("#ltxtReceivingDepartment").val("");
$("#ltxtShipper").val("");
$("#ltxtReceiver").val("");
$("#ltxtMake_bills").val("");
$("#ltxtMake_bills_time").val("");
$("#ltxtTransactor").val("");
$("#ltxtExecute_time").val("");
$("#ltxtRemarks").val("");
$("#txtRecordNumber").val("");
$("#txtRemarks").val("");
$("#cobtrConsignmentDepartment").combobox('clear');
$("#cboReceivingDepartment").combobox('clear');
$("#dtmStart").datebox('clear');
$("#dtmEnd").datebox('clear');
}
//查询批次
function getPatch(index, data) {
$.getJSON("/Allot/SelectPatchMessage?intDetailId=" + data.Allot_detail_id, function (data1) {
$("#tbBatch2").datagrid("loadData", data1);
});
}
2.3、Csharp-Controller-convertHelper代码:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Web.Mvc;
namespace 大型商贸系统.Controllers
{
public class ConvertHelperController : Controller
{
//
// GET: /ConvertHelper/
/// <summary>
///
/// </summary>
/// <param name="dt">传入的DataTable</param>
/// <returns>返回泛型数据类型</returns>
#region 将DataTable转换成List<Dictionary<string, object>>数据类型
public static List<Dictionary<string, object>> DtToList(DataTable dt)
{
return (from DataRow dr in dt.Rows
select dt.Columns.Cast<DataColumn>().ToDictionary(dc => dc.ColumnName, dc => dr[dc.ColumnName])).ToList();
}
#endregion
/// <summary>
///
/// </summary>
/// <param name="str">传入的字符串</param>
/// <returns>返回‘0’,或转换成整形后的数据</returns>
public static int ConverToInt(string str)
{
return str.Trim() != "" ? Convert.ToInt32(str) : 0;//三元符:条件?成立(返回):不成立(返回)
}
/// <summary>
///
/// </summary>
/// <param name="str">输入的字符串类型日期</param>
/// <returns>返回日期</returns>
public static DateTime StarDate(string str)
{
return str.Trim() != "" ? Convert.ToDateTime(str) : Convert.ToDateTime("1888-10-26");//如果为空返回"1888-10-26",如果不为空返回转换后的str
}
/// <summary>
///
/// </summary>
/// <param name="str">输入的字符串类型日期</param>
/// <returns>返回日期</returns>
public static DateTime EndDate(string str)
{
return str.Trim() != "" ? Convert.ToDateTime(str) : Convert.ToDateTime("9999-10-26");
}
/// <summary>
///
/// </summary>
/// <param name="str">传入的密码</param>
/// <returns>返回MD5</returns>
#region MD5加密密码
public static string ToMd5(string str)
{
MD5 md5 = MD5.Create(); //创建哈希算法
byte[] s = md5.ComputeHash(Encoding.UTF8.GetBytes(str)); //获取utf-8编码格式字符的字节
string pwd = s.Aggregate("", (current, t) => current + t.ToString("x")); //用累加器累加
return pwd;
}
#endregion
#region 获取拼音简码
public static string GetChineseSpell(string strText)
{
int len = strText.Length; //获取输入的字符长度
string myStr = "";
for (int i = 0; i < len; i++)
{
myStr += GetSpell(strText.Substring(i, 1));
}
return myStr;
}
private static string GetSpell(string cnChar)
{
byte[] arrCn = Encoding.Default.GetBytes(cnChar);
if (arrCn.Length > 1)
{
int area = (short) arrCn[0];
int pos = (short) arrCn[1];
int code = (area << 8) + pos;
int[] areacode =
{
45217, 45253, 45761, 46318, 46826, 47010,
47297, 47614, 48119, 48119, 49062, 49324,
49896, 50371, 50614, 50622, 50906, 51387,
51446, 52218, 52698, 52698, 52698, 52980,
53689, 54481
};
for (int i = 0; i < 26; i++)
{
int max = 55290;
if (i != 25) max = areacode[i + 1];
if (areacode[i] <= code && code < max)
{
return Encoding.Default.GetString(new byte[] {(byte) (65 + i)});
}
}
return "*";
}
return cnChar;
}
#endregion
#region 生成部门下拉树
public static List<Dictionary<string, object>> Child(DataTable dt, int fid = 0)
{
var listReturn = new List<Dictionary<string, object>>();//实例化一个泛型
var dv = new DataView(dt) { RowFilter = "Department_Farid=" + fid };//初始化DataView类,根据列‘Department_Farid’筛选行
if (dv.Count > 0)//如果筛选出有记录
{
DataTable dtChild = dv.ToTable();//将筛选出来的行转化为DataTable
for (int i = 0; i < dtChild.Rows.Count; i++)//遍历DataTable的每行
{
int departmentId = Convert.ToInt32(dtChild.Rows[i]["Department_id"]);
string departmentName = dtChild.Rows[i]["Department_name"].ToString();
var childList = new Dictionary<string, object> { { "id", departmentId }, { "text", departmentName } };//实例化一个字典构建{键名:键值}类型数据
if (Child(dt, departmentId) != null)//调用Child()的方法,如何有数据,就添加子节点。
{
childList.Add("state", "closed");
childList.Add("children", Child(dt, departmentId));
}
listReturn.Add(childList);//将字典添加到字典泛型
}
}
else
{
return null;//如果筛选没数据就返回null
}
return listReturn;
}
#endregion
#region 部门名称拼接
public static string DepartmentNameSelect(DataTable dt, int intDeparId)
{
var dv = new DataView(dt) { RowFilter = "Department_id=" + intDeparId };//将DataTable转换成DataView,并根据列【Department_id】等于变量id进行筛选选择的那条数据
DataTable dtChild = dv.ToTable();//将筛选出来的记录转换为DataTable
int farid = Convert.ToInt32(dtChild.Rows[0]["Department_Farid"]);//获取选择的那项的父id
string sonName = dtChild.Rows[0]["Department_name"].ToString().Trim() + "【" + dtChild.Rows[0]["Department_code"].ToString().Trim() + "】";//获取末级部门名称并拼接上代码。
string names = FarDeparName(dt, farid);//调用FarDeparName()方法进一步筛选上一级的部门名称
return names + sonName.Trim();//拼接方法FarDeparName()返回的字符串和末级部门名称
}
private static string FarDeparName(DataTable dt, int id)
{
StringBuilder names = new StringBuilder();//实例化字符串拼接器
if (id > 0)
{
var dv = new DataView(dt) { RowFilter = "Department_id=" + id };//根据部门id等于下一级的父id作为条件筛选
DataTable dtChild = dv.ToTable();
string name = dtChild.Rows[0]["Department_name"].ToString().Trim() + "→";//获取部门名称拼接上"→";
int cid = Convert.ToInt32(dtChild.Rows[0]["Department_Farid"]);//获取部门父id
names.Append((FarDeparName(dt, cid)) + name);//将获取的部门名称拼接到后面,下一次循环拼接到前面
}
return names.ToString();
}
#endregion
}
}
2.4、Csharp-Controller- AllotController代码
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Web.Mvc;
using BLL_KuCunGuangLi;
namespace 大型商贸系统.Controllers
{
public class AllotController : Controller
{
//
// GET: /Allot/
readonly IDiaoBos _myDiaoBo = new DiaoBo();
#region 调拨单处理
public ActionResult DealWithAllot()
{
return View();
}
#region 部门名称拼接
public ActionResult DepartmentNameSelect(int intDeparId)
{
DataTable dt = _myDiaoBo.GetAllDepartment();//查询全部部门
string names = ConvertHelperController.DepartmentNameSelect(dt, intDeparId);//调用已定义的ConvertHelperController控制器中的静态方法拼接出部门归属
return Json(names, JsonRequestBehavior.AllowGet);//将返回的string转换为json数据类型,设置请求为允许返回数据
}
#endregion
#region 审核调拨单
public ActionResult ReViewAllot(int intAllotId)
{
StringBuilder msg = new StringBuilder();//实例化字符拼接器
DataTable dtAllot = _myDiaoBo.SelectAllotDetailByAllotId_reView(intAllotId);//根据调拨id查询到该条调拨单的明细
for (int i = 0; i < dtAllot.Rows.Count; i++)
{
int allotDetailId = Convert.ToInt32(dtAllot.Rows[i]["Allot_detail_id"]);//获取调拨明细id,并转换为整形
DataTable sPatch = _myDiaoBo.SelectAllotPatchIdByAllotDetailId(allotDetailId);//根据调拨明细id查找批次表
if (sPatch.Rows.Count == 0)//如果查找不到批记录,说明还没添加批次。
{
return Json("false", JsonRequestBehavior.AllowGet);//返回字符“false”
}
int quantity = Convert.ToInt32(dtAllot.Rows[i]["Shipments_quantity"]);
string goodsName = dtAllot.Rows[i]["Goods_name"].ToString();
int stockQuantity = Convert.ToInt32(dtAllot.Rows[i]["Stock_quantity"]);
if (stockQuantity < quantity)//判断库存数是否小于发货数
{
msg.Append("商品【" + goodsName + "】库存数为【<span style='font-size:15px;color:red'>" + stockQuantity +
"</span>】,库存不足+");
}
}
if (msg.ToString() == "")
{
for (int i = 0; i < dtAllot.Rows.Count; i++)
{
int quantity = Convert.ToInt32(dtAllot.Rows[i]["Shipments_quantity"]);
int stockId = Convert.ToInt32(dtAllot.Rows[i]["Stock_id"]);
_myDiaoBo.UpdateStockQuantity(stockId, quantity);//根据库存id修改库数
}
_myDiaoBo.UpdateAllotToreView(intAllotId);//修改调拨单为已审核
return Json("true", JsonRequestBehavior.AllowGet);
}
msg.Append("审核失败!原因如下:+" + msg);
return Json(msg.ToString(), JsonRequestBehavior.AllowGet);
}
#endregion
#region 修改批次
public ActionResult UpdatePatch(int intPatchId, int quantity)
{
int k = _myDiaoBo.UpdatePatch(intPatchId, quantity);
return Json(k, JsonRequestBehavior.AllowGet);
}
#endregion
#region 获取所有该商品在该进货部门的进货批次
public ActionResult GetallEnterPatch(int placeId, int goodId, int detailId)
{
DataTable sAllotDetail = _myDiaoBo.SelectPatchMessage(detailId);//获取批次中的库存批次信息
DataTable dt = _myDiaoBo.GetStockQuantity(placeId, goodId);//获取所有库存批次信息
for (int i = 0; i < sAllotDetail.Rows.Count; i++)
{
int stockIdPatch = Convert.ToInt32(sAllotDetail.Rows[i]["Stock_id"]);
int stockId = Convert.ToInt32(dt.Rows[i]["Stock_id"]);
if (stockId == stockIdPatch)//如果查询出的所有库存批次中,已分批的,则从所有库存批次表中移除
{
dt.Rows.Remove(dt.Rows[i]);
}
}
for (int i = 0; i < dt.Rows.Count; i++)//将剩下的所有库存批次复制到配货批次表中
{
sAllotDetail.ImportRow(dt.Rows[i]);
}
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(sAllotDetail);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 自动分配
public ActionResult AutoCreatePatch(string pm)
{
bool alow = true;
StringBuilder msg = new StringBuilder();
string[] rows = pm.Split('.');
List<int> reperIds = new List<int>();
List<int> goodIds = new List<int>();
List<string> goodNames = new List<string>();
List<int> allotDetailIds = new List<int>();
List<int> unallots = new List<int>();
for (int i = 0; i < rows.Length - 1; i++)
{
string[] values = rows[i].Split(',');
int allotDetailId = Convert.ToInt32(values[0]);//调拨明细id
string goodName = values[4];//商品名称
int goodsId = Convert.ToInt32(values[1]);//商品id
int repertoryPlace = Convert.ToInt32(values[2]);//库存地点id
int unallot = Convert.ToInt32(values[3]);//配货数量
DataTable dtCheckPatch = _myDiaoBo.SelectPatchMessage(allotDetailId);//查询批次信息
if (dtCheckPatch.Rows.Count > 0)
{
msg.Append("商品【<span style='color:red'>" + goodName.Trim() + "</span>】已经分配,不能重新分配!+");//如果可以查到批次,说明已分批,则拼接提示信息
continue;//跳过当次批次
}
DataTable dt = _myDiaoBo.CheckStockOfConsignmentDepartment(repertoryPlace, goodsId);//查询发货部门的库存
int allStock = 0;
if (dt.Rows.Count > 0)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
allStock += Convert.ToInt32(dt.Rows[j]["Stock_quantity"]);//求所有批次的库存数之和
}
if (allStock > unallot)//如果总库存数大于要调拨的数,则可以调拨
{
reperIds.Add(repertoryPlace);//将库存地点添加到泛型列
goodIds.Add(goodsId);
goodNames.Add(goodName);
allotDetailIds.Add(allotDetailId);
unallots.Add(unallot);
}
else
{
msg.Append("商品【<span style='color:red'>" + goodName.Trim() + "</span>】库存不足,不能分配!+");
alow = false;
}
}
else
{
msg.Append("商品【<span style='color:red'>" + goodName.Trim() + "</span>】没有库存,不能分配!+");
alow = false;
}
}
if (alow)
{
for (int i = 0; i < reperIds.Count; i++)
{
DataTable selStock = _myDiaoBo.CheckStockOfConsignmentDepartment(reperIds[i], goodIds[i]);
for (int j = 0; j < selStock.Rows.Count; j++)//将所有的泛型列按放入的顺序获取出来
{
int stockQuantity = Convert.ToInt32(selStock.Rows[j]["Stock_quantity"]);
int stockIds = Convert.ToInt32(selStock.Rows[j]["Stock_id"]);
if (unallots[i] > stockQuantity)//如果配货数量大于该库数量
{
int patchId = NewPatchNumber(goodNames[i], goodIds[i]);//新增批次号
_myDiaoBo.AutoCreatePatch(patchId, stockQuantity, allotDetailIds[i], stockIds);//新增批次,配货数量为库存数量
unallots[i] -= stockQuantity;//配货数减去该库存数然后赋值给未配货数
}
else
{
int patchId = NewPatchNumber(goodNames[i], goodIds[i]);
_myDiaoBo.AutoCreatePatch(patchId, unallots[i], allotDetailIds[i], stockIds);//如果库存数大于配货数,配货数量为配货数,
break;//终止当前循环
}
}
}
}
if (msg.ToString().Contains("不能分配"))
{
return Json("false+" + msg, JsonRequestBehavior.AllowGet);
}
if (msg.ToString().Contains("已经分配"))
{
return Json("true+" + msg, JsonRequestBehavior.AllowGet);
}
return Json("+" + msg, JsonRequestBehavior.AllowGet);
}
#endregion
#region 新增批次号
private int NewPatchNumber(string goodsName, int goodsId = 0)
{
DataTable dt = _myDiaoBo.GetOddNumbers(1);//获取调拨的批次前缀
DataTable dt2 = _myDiaoBo.SelectDayMaxCountOfPatc(goodsId);//获取商品批次的最大数
int patchCount = 0;
if (dt2.Rows.Count == 0)//如果没有批号记录就新增一条记录
{
_myDiaoBo.AddPatchDayMaxCount(goodsId);
}
else
{
patchCount = (int)dt2.Rows[0][0];//如果有记录就获取最大数
}
string aa = ConvertHelperController.GetChineseSpell(goodsName).Substring(0, 2);//获取拼音简码的前两位
string patchNumber = aa + dt.Rows[0]["Prefixs"].ToString().Trim();//将拼音码拼接到批次号前面
int count = Convert.ToInt32(dt.Rows[0]["Suffix_number"]);//拼接的位数
string patchCounts = patchCount.ToString("D" + count + "");//patchCount前面拼接0,使之位数等于count
string recordNumber = patchNumber + patchCounts;//批次号由批次号前缀+最大数
DataTable dt4 = _myDiaoBo.CheckPatchNumber(goodsId);//根据商品id查询配货批次
DataTable dt6;
if (dt4.Rows.Count > 0)
{
int id = Convert.ToInt32(dt4.Rows[0][0]);//获取日最大单数的id
dt6 = _myDiaoBo.SavePatchNumber(recordNumber, id);//保存批次号
}
else
{
DataTable dt5 = _myDiaoBo.AddPatchDayMaxCount(goodsId);//如果没有批次记录,新增批次记录
int id = Convert.ToInt32(dt5.Rows[0][0]);
dt6 = _myDiaoBo.SavePatchNumber(recordNumber, id);
}
int k = Convert.ToInt32(dt6.Rows[0][0]);
return k;//返回生成的批次的id
}
#endregion
#region 查询批次信息
public ActionResult SelectPatchMessage(int intDetailId)
{
DataTable selecTable = _myDiaoBo.SelectPatchMessage(intDetailId);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(selecTable);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 创建调拨单号
public ActionResult GetRecord_number()
{
DataTable dt = _myDiaoBo.GetOddNumbers(4);//获取调拨单号前缀和字符串拼接的个数
DataTable dt2 = _myDiaoBo.GetDayMaxCount(4);//获取调拨单的日最大单数
string patchNumber = dt.Rows[0]["Prefixs"].ToString().Trim();//获取前缀
int count = Convert.ToInt32(dt.Rows[0]["Suffix_number"].ToString().Trim());//获取拼接的字符串的位数
string patchCount = (Convert.ToInt32(dt2.Rows[0][0]) + 1).ToString("D" + count + "");//在最大数前拼接”0“,使之为数达到count
string recordNumber = patchNumber + patchCount;//拼接调拨单号完成
return Json(recordNumber, JsonRequestBehavior.AllowGet);
}
#endregion
#region 获取发货部门(下拉树)
public ActionResult GetConsignmentDepartmentAll()
{
DataTable dt = _myDiaoBo.GetConsignmentDepartmentAll(); //查询所有的商品信息
return Json(ConvertHelperController.Child(dt), JsonRequestBehavior.AllowGet); //返回Joson字符串
}
#endregion
#region 获取发货部门
public ActionResult GetReceivingDepartmentById(int intDepartmenId)
{
DataTable dt = _myDiaoBo.GetReceivingDepartmentById(intDepartmenId);//根据部门id,查询出同级的部门
for (int i = 0; i < dt.Rows.Count; i++)//遍历每一行
{
if (Convert.ToInt32(dt.Rows[i]["id"]) == intDepartmenId)//筛选出部门id与intDepartmenId相等的项
{
dt.Rows.Remove(dt.Rows[i]);//从datagrid中移除id与intDepartmenId相等的行
}
}
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 根据部门ID查找部门员工
public ActionResult DepartmentStaffSelectbyDepartmentId(int intDepartmentFarId)
{
DataTable dt = _myDiaoBo.DepartmentStaffSelectbyDepartmentId(intDepartmentFarId);
List<Dictionary<string, object>> yuanGong = ConvertHelperController.DtToList(dt);
return Json(yuanGong, JsonRequestBehavior.AllowGet);
}
#endregion
#region 获取全部商品信息
public ActionResult GoodsAllSelect(int id, string strarry)
{
DataTable dt = _myDiaoBo.GoodsAllSelect(id);//查询所以商品
string[] str = strarry.Split(',');//以‘,’为标记拆分传过来的商品id数组
foreach (string t in str)//遍历没一个元素
{
for (int i = 0; i < dt.Rows.Count; i++)//遍历每一行商品
{
if (t == dt.Rows[i]["Goods_id"].ToString())//筛选出商品明细已存在的商品
{
dt.Rows.Remove(dt.Rows[i]);//移除商品明细已存在的商品
}
}
}
List<Dictionary<string, object>> shangPinXinXiAll = ConvertHelperController.DtToList(dt);
return Json(shangPinXinXiAll, JsonRequestBehavior.AllowGet);
}
public ActionResult GoodsAllSelect1(int id)
{
DataTable dt = _myDiaoBo.GoodsAllSelect(id);
List<Dictionary<string, object>> shangPinXinXiAll = ConvertHelperController.DtToList(dt);
return Json(shangPinXinXiAll, JsonRequestBehavior.AllowGet);
}
#endregion
#region 模糊查询商品信息
public ActionResult BlurSelect(string strMoHu)
{
DataTable dt = _myDiaoBo.BlurSelect(strMoHu);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region get收发货部门仓库地点
public ActionResult RepertoryPlaceSendSelect(int buMeId)
{
DataTable dt = _myDiaoBo.RepertoryPlaceSendSelect(buMeId);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
public ActionResult RepertoryPlaceHarvestSelect(int buMeId)
{
DataTable dt = _myDiaoBo.RepertoryPlaceHarvestSelect(buMeId);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 调拨的制单人,执行人
public ActionResult GetEmployeeByDepartId(int intDepartmenId)
{
DataTable dt = _myDiaoBo.GetEmployeeByDepartId(intDepartmenId);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 新增调拨单
public ContentResult SaveAllot(string strRecordNumber, string intConsignmentDepartment,
string intReceivingDepartment,
string intShipperId,
string intReceiverId, string strRemarks, string intMakeBillsId, string dtmMakeBills, string intTransactorId,
string dtmExecute)
{
DataTable dt = _myDiaoBo.SaveAllot(
strRecordNumber,
Convert.ToInt32(intConsignmentDepartment),
Convert.ToInt32(intReceivingDepartment),
Convert.ToInt32(intShipperId),
Convert.ToInt32(intReceiverId),
strRemarks,
Convert.ToInt32(intMakeBillsId),
Convert.ToDateTime(dtmMakeBills),
Convert.ToInt32(intTransactorId),
Convert.ToDateTime(dtmExecute)
);
return Content((dt.Rows[0][0].ToString()));
}
#endregion
#region 查询所有调拨单编号
public ActionResult GetAllRecordNumberOfAllot(string strMoHu)
{
DataTable dt = _myDiaoBo.GetAllRecordNumberOfAllot(strMoHu);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 查询调拨单by调拨ID
public ActionResult SelectAllotByAllotId(int intAllotId)
{
DataTable dt = _myDiaoBo.SelectAllotByAllotId(intAllotId);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 查询调拨单明细by调拨单ID_新增
public ActionResult SelectAllotDetailByAllotId(int intAllotId)
{
DataTable dt = _myDiaoBo.SelectAllotDetailByAllotId(intAllotId);
dt.Columns.Add("Repertory_enter_packages", typeof(int));//新增【件数】列
dt.Columns.Add("Tax_inclusive_price", typeof(decimal));//新增【含税进价】列
for (int i = 0; i < dt.Rows.Count; i++)
{
decimal caiGouJinJia = Convert.ToDecimal(dt.Rows[i]["Purchase_bid"]);//获取进价
decimal jinXianShuiLv = Convert.ToDecimal(dt.Rows[i]["Input_ratio"]);//获取进项税率
int shipmentsQuantity = Convert.ToInt32(dt.Rows[i]["Shipments_quantity"]);//获取数量
int zhiDiHanLiang = Convert.ToInt32(dt.Rows[i]["Quality_content"]);//获取质地含量
dt.Rows[i]["Tax_inclusive_price"] = caiGouJinJia * (jinXianShuiLv / 100 + 1);//计算含税进价
dt.Rows[i]["Repertory_enter_packages"] = shipmentsQuantity / zhiDiHanLiang;//计算件数
}
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 删除调拨单by调拨单id
public int DeleteAllotByAllotId(int intAllotId)
{
return _myDiaoBo.DeleteAllotByAllotId(intAllotId);//返回影响行数
}
#endregion
#region 修改调拨单明细
//新增调拨明细
public int SaveAllotDetail(string intAllotId, string intGoodsId, string strShipmentsQuantity,
string intRepertoryPlaceSendId, string intRepertoryPlaceHarvestId)
{
return _myDiaoBo.SaveAllotDetail(
Convert.ToInt32(intAllotId),
Convert.ToInt32(intGoodsId),
strShipmentsQuantity,
Convert.ToInt32(intRepertoryPlaceSendId),
Convert.ToInt32(intRepertoryPlaceHarvestId)
);
}
//删除调拨明细
public int DeleteDiaoBoMingXiByDiaoBoMingXiId(int intAllotDetailId)
{
return _myDiaoBo.DeleteDiaoBoMingXiByDiaoBoMingXiId(intAllotDetailId);//根据调拨明细id删除调拨明细
}
//修改调拨明细
public int UpdateDiaoBoDanMingXi(string intAllotDetailId, string strShipmentsQuantity,
string intRepertoryPlaceSendId,
string intRepertoryPlaceHarvestId)
{
return _myDiaoBo.UpdateDiaoBoDanMingXi(Convert.ToInt32(intAllotDetailId),
strShipmentsQuantity,
Convert.ToInt32(intRepertoryPlaceSendId),
Convert.ToInt32(intRepertoryPlaceHarvestId));
}
#endregion
#region 修改调拨单
public int UpdateDiaoBoDan(string intdiaoBoId,
string intConsignmentDepartmentId,
string intReceivingDepartmentId,
string intShipperId,
string intReceiverId,
string strRemarks,
string intMakeBillsId,
string intTransactorId,
string dtmMakeBillsTime,
string dtmExecuteTime)
{
return _myDiaoBo.UpdateDiaoBoDan(Convert.ToInt32(intdiaoBoId),
Convert.ToInt32(intConsignmentDepartmentId),
Convert.ToInt32(intReceivingDepartmentId),
Convert.ToInt32(intShipperId),
Convert.ToInt32(intReceiverId),
strRemarks,
Convert.ToInt32(intMakeBillsId),
Convert.ToInt32(intTransactorId),
Convert.ToDateTime(dtmMakeBillsTime),
Convert.ToDateTime(dtmExecuteTime));
}
#endregion
#region 查询收货部门库存
public ActionResult CheckStockOfReceivingDepartment(string intRepertoryPlaceSendId,
string intGoodsId)
{
DataTable dt = _myDiaoBo.CheckStockOfReceivingDepartment(Convert.ToInt32(intRepertoryPlaceSendId),
Convert.ToInt32(intGoodsId));
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#endregion
#region 查询调拨单
public ActionResult SelectAllot()
{
return View();
}
#region 多条件动态查询调拨单
public ActionResult SelectAllAllotOfReview(
string strRecordNumber,
string intConsignmentDepartment,
string intReceivingDepartment,
string dtmStart,
string dtmEnd,
string strRemarks,
bool blnCheck)
{
DataTable dt = _myDiaoBo.SelectAllAllotOfReview(
strRecordNumber,
ConvertHelperController.ConverToInt(intConsignmentDepartment),
ConvertHelperController.ConverToInt(intReceivingDepartment),
ConvertHelperController.StarDate(dtmStart),
ConvertHelperController.EndDate(dtmEnd),
strRemarks,
blnCheck);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 根据调拨单id查询调拨单
public ActionResult GetAllot(int intAllotId)
{
DataTable dt = _myDiaoBo.GetAllot(intAllotId);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 根据调拨单id查询调拨明细
public ActionResult GetAllotDetail(int intAllotId)
{
DataTable dt = _myDiaoBo.GetAllotDetail(intAllotId);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#endregion
#region 销售部门调整处理
public ActionResult DealWithSalesDepartmentAdjust()
{
return View();
}
#region 启动
public ActionResult StartoverSalesDepartment(int intSalesDepartment, int intStartover, DateTime dtmStartoverTime)
{
DataTable dt = _myDiaoBo.GetSalesDepartmentById(intSalesDepartment);
if (dt.Rows.Count > 0)
{
int reinId = Convert.ToInt32(dt.Rows[0]["Reinstated_department_id"]);//获取原部门
int newId = Convert.ToInt32(dt.Rows[0]["New_department_id"]);//获取新部门
for (int i = 0; i < dt.Rows.Count; i++)
{
int goodId = Convert.ToInt32(dt.Rows[i]["Goods_id"]);//获取商品id
_myDiaoBo.StartoverSalesDepartments(reinId, goodId, newId);
}
}
int k = _myDiaoBo.StartoverSalesDepartment(intSalesDepartment, intStartover, dtmStartoverTime);
return Json(k, JsonRequestBehavior.AllowGet);
}
#endregion
#region 审核
public ActionResult AuditingSalesDepartment(int intSalesDepartment, int intAuditor, DateTime dtmAuditorTime)
{
DataTable dt = _myDiaoBo.GetSalesDepartmentBySalesDeparId(intSalesDepartment);
if (dt.Rows[0]["Review_deny"].ToString() == "True")
{
return Json("false", JsonRequestBehavior.AllowGet);
}
int k = _myDiaoBo.AuditingSalesDepartment(intSalesDepartment, intAuditor, dtmAuditorTime);
return Json(k, JsonRequestBehavior.AllowGet);
}
#endregion
#region 获取销售部门调整单
public ActionResult GetSalesDepartmentAdjustment(int intSalesDepartmentAdjustmentId)
{
DataTable dt = _myDiaoBo.GetSalesDepartmentAdjustment(intSalesDepartmentAdjustmentId);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 修改销售部门调整单
public ActionResult UpdateSalesDepartmentAdjustment(
string strRecordNumber,
int intNewDepartMent,
int intReinstatedDepartment,
DateTime dtmStartoverTime,
int intMakeBills,
DateTime dtmMakeBills,
int inttSalesDepartmentAdjustmentId
)
{
DataTable dt = _myDiaoBo.CheckShenHeFou(inttSalesDepartmentAdjustmentId);
if (Convert.ToBoolean(dt.Rows[0][0]))//检查是否已审核
{
return Json("false", JsonRequestBehavior.AllowGet);
}
_myDiaoBo.DeleteSalesDepartmentAdjustDetail(inttSalesDepartmentAdjustmentId);
int i = _myDiaoBo.UpdateSalesDepartmentAdjustment(
strRecordNumber,
intNewDepartMent,
intReinstatedDepartment,
dtmStartoverTime,
intMakeBills,
dtmMakeBills,
inttSalesDepartmentAdjustmentId
);
return Json(i, JsonRequestBehavior.AllowGet);
}
#endregion
#region 绑定部门调整明细
public ActionResult BindingSalesDepartmentAdjustDetail(int intSalesDepartmentAdjustmentId)
{
DataTable dt = _myDiaoBo.BindingSalesDepartmentAdjustDetail(intSalesDepartmentAdjustmentId);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 删除部门调整、调整明细
public ActionResult DeleteSalesDepartmentAdjustDetail(int intSalesDepartmentAdjustmentId)
{
_myDiaoBo.DeleteSalesDepartmentAdjustDetail(intSalesDepartmentAdjustmentId);
int k = _myDiaoBo.DeleteSalesDepartmentAdjustment(intSalesDepartmentAdjustmentId);
return Json(k, JsonRequestBehavior.AllowGet);
}
#endregion
#region 获取全部销售部门
public ActionResult GetAllSalesDepartment(int type)
{
DataTable dt = type == 1 ? _myDiaoBo.GetAllSalesDepartment1() : _myDiaoBo.GetAllSalesDepartment2();//三元符,如果type=1,执行_myDiaoBo.GetAllSalesDepartment1()
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt); //,否则执行_myDiaoBo.GetAllSalesDepartment2()
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 新增部门调整明细
public void InsertSalesDepartmentAdjustDetail(int intSalesDepartmentAdjustmentid, int intGoodsId, decimal decStockQuantity)
{
_myDiaoBo.InsertSalesDepartmentAdjustDetail(intSalesDepartmentAdjustmentid, intGoodsId, decStockQuantity);
}
#endregion
#region 新增部门调整单
public ActionResult InsertSalesDepartmentAdjustment(
string strRecordNumber,
string intNewDepartMent,
string intReinstatedDepartment,
string dtmStartoverTime,
string intMakeBills,
string dtmMakeBills)
{
DataTable dt = _myDiaoBo.InsertSalesDepartmentAdjustment(
strRecordNumber,
Convert.ToInt32(intNewDepartMent),
Convert.ToInt32(intReinstatedDepartment),
Convert.ToDateTime(dtmStartoverTime),
Convert.ToInt32(intMakeBills),
dtmMakeBills);
int intSalesDepartmentAdjustmentid = Convert.ToInt32(dt.Rows[0][0]);
return Json(intSalesDepartmentAdjustmentid, JsonRequestBehavior.AllowGet);
}
#endregion
#region 不定条件查询商品信息
public ActionResult SelectGoodsOfReinstatedDepartMentByVariedTerm(
string intGoodsClassify,
string intSupplyUnits,
string intGoodsRadeMark,
string strGoodsCode,
string strGoodsName,
string intReinstatedDepartMentId)
{
DataTable dt = _myDiaoBo.SelectGoodsOfReinstatedDepartMentByVariedTerm(
ConvertHelperController.ConverToInt(intGoodsClassify),
ConvertHelperController.ConverToInt(intSupplyUnits),
ConvertHelperController.ConverToInt(intGoodsRadeMark),
strGoodsCode,
strGoodsName,
ConvertHelperController.ConverToInt(intReinstatedDepartMentId)
);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 根据原部门绑定部门所拥有商品的商标
public ActionResult GetGoodsRademarkByReinstatedDepartMentGoods(int intReinstatedDepartMentId)
{
DataTable dt = _myDiaoBo.GetGoodsRademarkByReinstatedDepartMentGoods(intReinstatedDepartMentId);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 根据部门查询往来单位
public ActionResult GetSupplyUnitsOfReinstatedDepartMentGoods(int intReinstatedDepartMentId)
{
DataTable dt = _myDiaoBo.GetSupplyUnitsOfReinstatedDepartMentGoods(intReinstatedDepartMentId);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 生成销售部门调整记录编号(与调拨单生成一样)
public ActionResult NewSalesDepartmentRecordNumber()
{
DataTable dt = _myDiaoBo.GetOddNumbers(2);
DataTable dt2 = _myDiaoBo.GetDayMaxCount(2);
string patchNumber = dt.Rows[0]["Prefixs"].ToString().Trim();
int count = Convert.ToInt32(dt.Rows[0]["Suffix_number"].ToString().Trim());
string patchCount = (Convert.ToInt32(dt2.Rows[0][0]) + 1).ToString("D" + count + "");
string recordNumber = patchNumber + patchCount;
return Json(recordNumber, JsonRequestBehavior.AllowGet);
}
#endregion
#region 获取部门商品分类
public ActionResult GetGoodsClassifyOfReinstatedDepartMent(int intReinstatedDepartMentId)
{
DataTable dt = _myDiaoBo.GetGoodsClassifyOfReinstatedDepartMent(intReinstatedDepartMentId);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 根据属性明细id查询商品分类信息
public ActionResult GetGoodsClassifyByAttributeGatherDetailId(int intAttributeGatherDetailId)
{
DataTable dt = _myDiaoBo.GetGoodsClassifyByAttributeGatherDetailId(intAttributeGatherDetailId);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#endregion
#region 查询销售部门调整单
public ActionResult SelectSalesDepartmentAdjust()
{
return View();
}
#region 不定条件查询销售部门调整单
public ActionResult GetSalesDepartmentMessager(int intSalesDepartment, bool bolAuditingDeny,
bool bolStartoverDeny)
{
DataTable dt;
if (bolAuditingDeny && bolStartoverDeny) //如果既审核,又启动了
{
dt = _myDiaoBo.GetSalesDepartmentMessager1(intSalesDepartment);
}
else if (bolAuditingDeny) //如果只是审核,没启动
{
dt = _myDiaoBo.GetSalesDepartmentMessager2(intSalesDepartment);
}
else //既没审核,有没启动
{
dt = _myDiaoBo.GetSalesDepartmentMessager3(intSalesDepartment);
}
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 获取销售部门调整明细
public ActionResult GetSalesDepartmentDetails(int intSalesDepartment)
{
DataTable dt = _myDiaoBo.GetSalesDepartmentDetails(intSalesDepartment);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#region 不定条件查询销售部门
public ActionResult SelectSalesDepartmetAjustmentWithTrends(
string strRecordNumber,
string intConsignmentDepartment,
string intNewDepartment,
string dtmStart,
string dtmEnd,
bool bolAuditingDeny,
bool bolStartoverDeny
)
{
DataTable dt = _myDiaoBo.SelectSalesDepartmetAjustmentWithTrends(
strRecordNumber,
ConvertHelperController.ConverToInt(intConsignmentDepartment),
ConvertHelperController.ConverToInt(intNewDepartment),
ConvertHelperController.StarDate(dtmStart),
ConvertHelperController.EndDate(dtmEnd),
bolAuditingDeny,
bolStartoverDeny
);
List<Dictionary<string, object>> list = ConvertHelperController.DtToList(dt);
return Json(list, JsonRequestBehavior.AllowGet);
}
#endregion
#endregion
}
2.5、Csharp-BLL-Allot(接口)
using System;
using System.Data;
namespace BLL_KuCunGuangLi
{
public interface IDiaoBos
{
DataTable SelectAllotByAllotId(int intAllotId); //根据调拨单id查询调拨单所有信息
DataTable SelectAllotDetailByAllotId_reView(int intAllotId); //根据调拨单id查询调拨单-审核
void UpdateStockQuantity(int stockId, int quantity); //修改库存数-审核
void UpdateAllotToreView(int intAllotId); //修改调拨单位已审核
DataTable SelectPatchMessage(int allotDetailId); //查询配货批次信息
DataTable CheckStockOfConsignmentDepartment(int repertoryPlace, int goodsId); //检查发货部门库存
void AutoCreatePatch(int patchId, int quantum, int allotDetailId, int stockId); //自动生成批次
DataTable GetOddNumbers(int intOddNumbersId); //获取批次号/记录编号前缀
DataTable SelectDayMaxCountOfPatc(int goodsId); //获取日最大单数
DataTable AddPatchDayMaxCount(int goodsId); //新增批次记录
DataTable CheckPatchNumber(int goodsId); //检查批次号
DataTable SavePatchNumber(string recordNumber, int id); //保存批次号
DataTable BlurSelect(string strMoHu); //模糊查询商品信息
DataTable GoodsAllSelect(int id); //获取全部/按商品id查询商品信息
DataTable DepartmentStaffSelectbyDepartmentId(int intDepartmentFarId); //根据部门ID查找部门员工
DataTable GetAllRecordNumberOfAllot(string strMoHu); //查询所有调拨单编号
DataTable GetConsignmentDepartmentAll(); //查询所有的商品信息
DataTable GetReceivingDepartmentById(int intDepartmenId); //获取收货部门
DataTable RepertoryPlaceSendSelect(int buMeId); //get发货部门仓库地点
DataTable RepertoryPlaceHarvestSelect(int buMeId); //get收货部门仓库地点
DataTable GetEmployeeByDepartId(int intDepartmenId); // 调拨的制单人,执行人
DataTable SaveAllot(string strRecordNumber, int intConsignmentDepartment, int intReceivingDepartment,
int intShipperId, int intReceiverId, string strRemarks, int intMakeBillsId,
DateTime dtmMakeBills, int intTransactorId, DateTime dtmExecute); //新增调拨单
DataTable SelectAllotDetailByAllotId(int intAllotId); //根据调拨单id查询调拨单明细
DataTable SelectAllotPatchIdByAllotDetailId(int detailIds); //根据调拨明细id查询调拨批次
int DeleteAllotByAllotId(int intAllotId); //根据调拨id删除调拨单
int SaveAllotDetail(int intAllotId, int intGoodsId, string strShipmentsQuantity,
int intRepertoryPlaceSendId,
int intRepertoryPlaceHarvestId); //修改调拨单明细
int DeleteDiaoBoMingXiByDiaoBoMingXiId(int intAllotDetailId); //根据调拨明细id删除调拨明细
int UpdateDiaoBoDanMingXi(int intAllotDetailId, string strShipmentsQuantity, int intRepertoryPlaceSendId,
int intRepertoryPlaceHarvestId); //修改调拨明细
int UpdateDiaoBoDan(int intdiaoBoId,
int intConsignmentDepartmentId,
int intReceivingDepartmentId,
int intShipperId,
int intReceiverId,
string strRemarks,
int intMakeBillsId,
int intTransactorId,
DateTime dtmMakeBillsTime,
DateTime dtmExecuteTime); //修改调拨单
DataTable CheckStockOfReceivingDepartment(int p1, int p2); //查询收货部门库存
#region 不定条件查询销售部门调整单
DataTable GetSalesDepartmentMessager1(int intSalesDepartment);
DataTable GetSalesDepartmentMessager2(int intSalesDepartment);
DataTable GetSalesDepartmentMessager3(int intSalesDepartment);
#endregion
DataTable SelectAllAllotOfReview(string strRecordNumber, int intConsignmentDepartment,
int intReceivingDepartment, DateTime dtmStart, DateTime dtmEnd, string strRemarks, bool blnCheck);//多条件动态查询调拨单
DataTable GetAllot(int intAllotId);//根据调拨单id查询调拨单
DataTable GetAllotDetail(int intAllotId);//根据调拨单id查询调拨明细
int StartoverSalesDepartment(int intSalesDepartment, int intStartover, DateTime dtmStartoverTime);// 启动
int AuditingSalesDepartment(int intSalesDepartment, int intAuditor, DateTime dtmAuditorTime);//审核-销售部门调整
DataTable GetSalesDepartmentAdjustment(int intSalesDepartmentAdjustmentId);//获取销售部门调整单
int DeleteSalesDepartmentAdjustDetail(int inttSalesDepartmentAdjustmentId);//删除销售部门调整单明细
int UpdateSalesDepartmentAdjustment(string strRecordNumber, int intNewDepartMent, int intReinstatedDepartment, DateTime dtmStartoverTime, int intMakeBills, DateTime dtmMakeBills, int inttSalesDepartmentAdjustmentId);//修改销售部门调整单
DataTable BindingSalesDepartmentAdjustDetail(int intSalesDepartmentAdjustmentId);//绑定部门调整明细
int DeleteSalesDepartmentAdjustment(int intSalesDepartmentAdjustmentId);//删除部门调整
DataTable GetAllSalesDepartment1(); //查询全部未审核调整单
DataTable GetAllSalesDepartment2(); //查询全部未启动调整单
void InsertSalesDepartmentAdjustDetail(int intSalesDepartmentAdjustmentid, int intGoodsId, decimal decStockQuantity);// 新增部门调整明细
DataTable InsertSalesDepartmentAdjustment(string strRecordNumber, int intNewDepartMent,
int intReinstatedDepartment, DateTime dtmStartoverTime, int intMakeBills, string dtmMakeBills);//新增部门调整单
DataTable SelectGoodsOfReinstatedDepartMentByVariedTerm(int intGoodsClassify, int intSupplyUnits,
int intGoodsRadeMark, string strGoodsCode, string strGoodsName, int intReinstatedDepartMentId);//不定条件查询商品信息
DataTable GetGoodsRademarkByReinstatedDepartMentGoods(int intReinstatedDepartMentId);//根据原部门绑定部门所拥有商品的商标
DataTable GetSupplyUnitsOfReinstatedDepartMentGoods(int intReinstatedDepartMentId);//根据部门查询往来单位
DataTable GetDayMaxCount(int p);//生成销售部门调整记录编号
DataTable GetGoodsClassifyByAttributeGatherDetailId(int intAttributeGatherDetailId);//根据属性明细id查询商品分类信息
DataTable GetGoodsClassifyOfReinstatedDepartMent(int intReinstatedDepartMentId);//获取部门商品分类
DataTable GetSalesDepartmentDetails(int intSalesDepartment);//获取销售部门调整单明细
DataTable SelectSalesDepartmetAjustmentWithTrends(string strRecordNumber,
int intConsignmentDepartment,
int intNewDepartment,
DateTime dtmStart,
DateTime dtmEnd,
bool bolAuditingDeny,
bool bolStartoverDeny);//不定条件查询销售部门
int UpdatePatch(int intPatchId, int quantity);//保存修改-配送批次
DataTable GetSalesDepartmentById(int intSalesDepartment);//获取销售部门调整表by销售部门调整
void StartoverSalesDepartments(int reinId, int goodId, int newId);//启动销售部门调整
DataTable GetStockQuantity(int palceId, int goodsId);//查询同一部门同一商品的库存
DataTable CheckShenHeFou(int inttSalesDepartmentAdjustmentId);//检查部门销售单审核否
DataTable GetAllDepartment();//获取全部部门信息
DataTable GetSalesDepartmentBySalesDeparId(int intSalesDepartment);//根据销售部门调整id查询部门调整单
}
}
2.6、Csharp-BLL
using System;
using System.Data;
using System.Data.SqlClient;
using DALPublic;
namespace BLL_KuCunGuangLi
{
public class DiaoBo : IDiaoBos
{
readonly DALMethod _myAal = new DALMethod();
#region 调拨单处理
#region 根据部门ID查找部门员工
public DataTable DepartmentStaffSelectbyDepartmentId(int intDepartmentFarId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Department_id", SqlDbType.Int)
};
sql[0].Value = "DepartmentStaffSelectbyDepartmentId";
sql[1].Value = intDepartmentFarId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 获取全部/按商品id查询商品信息
public DataTable GoodsAllSelect(int id)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Goods_id", SqlDbType.Int)
};
sql[0].Value = "GoodsAllSelect";
sql[1].Value = id;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 模糊查询商品信息
public DataTable BlurSelect(string strMoHu)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Blur_content", SqlDbType.Char)
};
sql[0].Value = "BlurSelect";
sql[1].Value = strMoHu;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 获取部门所属所以仓库地点
#region 获取发货仓库地点
public DataTable RepertoryPlaceSendSelect(int buMeId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Department_id", SqlDbType.Int)
};
sql[0].Value = "RepertoryPlaceSendSelect";
sql[1].Value = buMeId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 获取收货仓库地点
public DataTable RepertoryPlaceHarvestSelect(int buMeId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Department_id", SqlDbType.Int)
};
sql[0].Value = "RepertoryPlaceHarvestSelect";
sql[1].Value = buMeId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#endregion
#region 根据发货部门查询制单人,执行人
public DataTable GetEmployeeByDepartId(int intDepartmenId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Department_id",SqlDbType.Int)
};
sql[0].Value = "GetEmployeeByDepartId";
sql[1].Value = intDepartmenId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 保存调拨单及调拨明细
public DataTable SaveAllot(string strRecordNumber, int intConsignmentDepartment, int intReceivingDepartment,
int intShipperId, int intReceiverId, string strRemarks, int intMakeBillsId,
DateTime dtmMakeBills, int intTransactorId, DateTime dtmExecute)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Record_number", SqlDbType.Char),
new SqlParameter("@Consignment_department_id", SqlDbType.Int),
new SqlParameter("@Receiving_department_id", SqlDbType.Int),
new SqlParameter("@Shipper_id", SqlDbType.Int),
new SqlParameter("@Receiver_id", SqlDbType.Int),
new SqlParameter("@Remarks", SqlDbType.Char),
new SqlParameter("@Make_bills_id", SqlDbType.Int),
new SqlParameter("@Make_bills_time", SqlDbType.DateTime),
new SqlParameter("@Transactor_id", SqlDbType.Int),
new SqlParameter("@Execute_time", SqlDbType.DateTime)
};
sql[0].Value = "SaveAllot";//参数1,对应存储过程的if(@NB=‘SaveAllot’)
sql[1].Value = strRecordNumber;
sql[2].Value = intConsignmentDepartment;
sql[3].Value = intReceivingDepartment;
sql[4].Value = intShipperId;
sql[5].Value = intReceiverId;
sql[6].Value = strRemarks;
sql[7].Value = intMakeBillsId;
sql[8].Value = dtmMakeBills;
sql[9].Value = intTransactorId;
sql[10].Value = dtmExecute;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 查询所有调拨单编号
public DataTable GetAllRecordNumberOfAllot(string strMoHu)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Blur_content", SqlDbType.Char)
};
sql[0].Value = "GetAllRecordNumberOfAllot";
sql[1].Value = strMoHu;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 根据调拨单id查询调拨单
#region 查询调拨单by调拨id
public DataTable SelectAllotByAllotId(int intAllotId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Allot_id", SqlDbType.Int)
};
sql[0].Value = "SelectAllotByAllotId";
sql[1].Value = intAllotId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 查询调拨明细信息及库存-审核
public DataTable SelectAllotDetailByAllotId_reView(int intAllotId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Allot_id", SqlDbType.Int)
};
sql[0].Value = "SelectAllotDetailByAllotId_reView";
sql[1].Value = intAllotId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 修改库存/修改调拨明细为已审核
public void UpdateStockQuantity(int stockId, int quantity)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Stock_id", SqlDbType.Int),
new SqlParameter("@Stock_quantity", SqlDbType.Int),
};
object[] param = { "UpdateStockQuantity", stockId, quantity};
for (int i = 0; i < sql.Length; i++)
{
sql[i].Value = param[i];
}
_myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 修改调拨单位已审核
public void UpdateAllotToreView(int intAllotId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Allot_id", SqlDbType.Int)
};
object[] param = { "UpdateAllotToreView", intAllotId };
for (int i = 0; i < sql.Length; i++)
{
sql[i].Value = param[i];
}
_myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#endregion
#region 根据调拨单id查询调拨单明细
public DataTable SelectAllotDetailByAllotId(int intAllotId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Allot_id", SqlDbType.Int)
};
sql[0].Value = "SelectAllotDetailByAllotId";
sql[1].Value = intAllotId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 删除调拨单/调拨单明细
public int DeleteAllotByAllotId(int intAllotId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Allot_id", SqlDbType.Int)
};
sql[0].Value = "DeleteAllotByAllotId";
sql[1].Value = intAllotId;
return _myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 检查发货部门库存
public DataTable CheckStockOfConsignmentDepartment(int intRepertoryPlaceSendId, int intGoodsId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Goods_id", SqlDbType.Int),
new SqlParameter("@Repertory_place_id", SqlDbType.Int)
};
sql[0].Value = "CheckStockOfConsignmentDepartment";
sql[1].Value = intGoodsId;
sql[2].Value = intRepertoryPlaceSendId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 检查收货部门库存
public DataTable CheckStockOfReceivingDepartment(int intRepertoryPlaceSendId, int intGoodsId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Repertory_place_id", SqlDbType.Int),
new SqlParameter("@Goods_id", SqlDbType.Int)
};
sql[0].Value = "CheckStockOfReceivingDepartment";
sql[1].Value = intRepertoryPlaceSendId;
sql[2].Value = intGoodsId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 查询所有的商品信息
public DataTable GetConsignmentDepartmentAll()
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char)
};
sql[0].Value = "GetConsignmentDepartmentAll";
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 获取收货部门(同级部门)
public DataTable GetReceivingDepartmentById(int intDepartmentFarId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB","GetReceivingDepartmentById"),
new SqlParameter("@Department_id", intDepartmentFarId)
};
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 获取日最大单数
public DataTable SelectDayMaxCountOfPatc(int goodsId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Goods_id", SqlDbType.Int)
};
sql[0].Value = "SelectDayMaxCountOfPatc";
sql[1].Value = goodsId;
return _myAal.DAL_SelectDB_Par("Update_Day_Max_Count", sql);
}
#endregion
#region 保存批次号
public DataTable SavePatchNumber(string patchNumber, int id)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Patch_number", SqlDbType.Char),
new SqlParameter("@Day_max_quantity_id", SqlDbType.Int)
};
sql[0].Value = "SavePatchNumber";
sql[1].Value = patchNumber;
sql[2].Value = id;
return _myAal.DAL_SelectDB_Par("Update_Day_Max_Count", sql);
}
#endregion
#region 修改调拨单/修改调拨单明细/新增调拨单明细
public int UpdateDiaoBoDan(int intdiaoBoId,
int intConsignmentDepartmentId,
int intReceivingDepartmentId,
int intShipperId,
int intReceiverId,
string strRemarks,
int intMakeBillsId,
int intTransactorId,
DateTime dtmMakeBillsTime,
DateTime dtmExecuteTime)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Consignment_department_id", SqlDbType.Int),
new SqlParameter("@Shipper_id", SqlDbType.Int),
new SqlParameter("@Receiving_department_id", SqlDbType.Int),
new SqlParameter("@Receiver_id", SqlDbType.Int),
new SqlParameter("@Make_bills_time", SqlDbType.DateTime),
new SqlParameter("@Execute_time", SqlDbType.DateTime),
new SqlParameter("@Make_bills_id", SqlDbType.Int),
new SqlParameter("@Remarks", SqlDbType.Char),
new SqlParameter("@Transactor_id", SqlDbType.Int),
new SqlParameter("@Allot_id", SqlDbType.Int)
};
sql[0].Value = "UpdateDiaoBoDan";
sql[1].Value = intConsignmentDepartmentId;
sql[2].Value = intShipperId;
sql[3].Value = intReceivingDepartmentId;
sql[4].Value = intReceiverId;
sql[5].Value = dtmMakeBillsTime;
sql[6].Value = dtmExecuteTime;
sql[7].Value = intMakeBillsId;
sql[8].Value = strRemarks;
sql[9].Value = intTransactorId;
sql[10].Value = intdiaoBoId;
return _myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#region 保存调拨单明细
public int SaveAllotDetail(int intAllotId, int intGoodsId, string strShipmentsQuantity,
int intRepertoryPlaceSendId,
int intRepertoryPlaceHarvestId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Allot_id", SqlDbType.Int),
new SqlParameter("@Goods_id", SqlDbType.Int),
new SqlParameter("@Shipments_quantity", SqlDbType.Char),
new SqlParameter("@Repertory_place_send_id", SqlDbType.Int),
new SqlParameter("@Repertory_place_harvest_id", SqlDbType.Int)
};
sql[0].Value = "SaveAllotDetail";
sql[1].Value = intAllotId;
sql[2].Value = intGoodsId;
sql[3].Value = strShipmentsQuantity;
sql[4].Value = intRepertoryPlaceSendId;
sql[5].Value = intRepertoryPlaceHarvestId;
return _myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 修改调拨明细
public int UpdateDiaoBoDanMingXi(int intAllotDetailId, string strShipmentsQuantity, int intRepertoryPlaceSendId,
int intRepertoryPlaceHarvestId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Allot_detail_id", SqlDbType.Int),
new SqlParameter("@Shipments_quantity", SqlDbType.Char),
new SqlParameter("@Repertory_place_send_id", SqlDbType.Int),
new SqlParameter("@Repertory_place_harvest_id", SqlDbType.Int)
};
sql[0].Value = "UpdateDiaoBoDanMingXi";
sql[1].Value = intAllotDetailId;
sql[2].Value = strShipmentsQuantity;
sql[3].Value = intRepertoryPlaceSendId;
sql[4].Value = intRepertoryPlaceHarvestId;
return _myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 根据调拨明细id删除调拨明细
public int DeleteDiaoBoMingXiByDiaoBoMingXiId(int intAllotDetailId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Allot_detail_id", SqlDbType.Int)
};
sql[0].Value = "DeleteDiaoBoMingXiByDiaoBoMingXiId";
sql[1].Value = intAllotDetailId;
return _myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#endregion
#endregion
#region 调拨单查询
#region 多条件动态查询调拨单
public DataTable SelectAllAllotOfReview(string strRecordNumber, int intConsignmentDepartment,
int intReceivingDepartment, DateTime dtmStart, DateTime dtmEnd, string strRemarks, bool blnCheck)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Record_number", SqlDbType.Char),
new SqlParameter("@Consignment_department_id", SqlDbType.Int),
new SqlParameter("@Receiving_department_id", SqlDbType.Int),
new SqlParameter("@dtmStart", SqlDbType.DateTime),
new SqlParameter("@dtmEnd", SqlDbType.DateTime),
new SqlParameter("@Remarks", SqlDbType.Char),
new SqlParameter("@Check", SqlDbType.Bit)
};
sql[0].Value = "SelectAllAllotOfReview";
sql[1].Value = strRecordNumber;
sql[2].Value = intConsignmentDepartment;
sql[3].Value = intReceivingDepartment;
sql[4].Value = dtmStart;
sql[5].Value = dtmEnd;
sql[6].Value = strRemarks;
sql[7].Value = blnCheck;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 根据调拨单id查询调拨单
public DataTable GetAllot(int intAllotId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Allot_id", SqlDbType.Int)
};
sql[0].Value = "GetAllot";
sql[1].Value = intAllotId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 根据调拨单id查询调拨明细
public DataTable GetAllotDetail(int intAllotId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Allot_id", SqlDbType.Int)
};
sql[0].Value = "GetAllotDetail";
sql[1].Value = intAllotId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#endregion
#region 销售部门调整处理
#region 获取批次号/记录编号前缀
public DataTable GetOddNumbers(int intOddNumbersId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Odd_numbers_id", SqlDbType.Int)
};
sql[0].Value = "GetOddNumbers";
sql[1].Value = intOddNumbersId;
return _myAal.DAL_SelectDB_Par("Update_Day_Max_Count", sql);
}
#endregion
#region 生成销售部门调整记录编号
public DataTable GetDayMaxCount(int dayMaxId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Day_max_quantity_id",SqlDbType.Int)
};
sql[0].Value = "GetDayMaxCount";
sql[1].Value = dayMaxId;
return _myAal.DAL_SelectDB_Par("Update_Day_Max_Count", sql);
}
#endregion
#region 获取部门商品分类
public DataTable GetGoodsClassifyOfReinstatedDepartMent(int intReinstatedDepartMentId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Department_id", SqlDbType.Int)
};
sql[0].Value = "GetGoodsClassifyOfReinstatedDepartMent";
sql[1].Value = intReinstatedDepartMentId;
sql[1].Value = intReinstatedDepartMentId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 根据属性明细id查询商品分类信息
public DataTable GetGoodsClassifyByAttributeGatherDetailId(int intAttributeGatherDetailId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Attribute_gather_detail_id", SqlDbType.Int)
};
sql[0].Value = "GetGoodsClassifyByAttributeGatherDetailId";
sql[1].Value = intAttributeGatherDetailId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 根据部门查询往来单位
public DataTable GetSupplyUnitsOfReinstatedDepartMentGoods(int intReinstatedDepartMentId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Department_id", SqlDbType.Int)
};
sql[0].Value = "GetSupplyUnitsOfReinstatedDepartMentGoods";
sql[1].Value = intReinstatedDepartMentId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 根据原部门绑定部门所拥有商品的商标
public DataTable GetGoodsRademarkByReinstatedDepartMentGoods(int intReinstatedDepartMentId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Department_id", SqlDbType.Int)
};
sql[0].Value = "GetGoodsRademarkByReinstatedDepartMentGoods";
sql[1].Value = intReinstatedDepartMentId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 不定条件查询商品信息
public DataTable SelectGoodsOfReinstatedDepartMentByVariedTerm(int intGoodsClassify, int intSupplyUnits,
int intGoodsRadeMark, string strGoodsCode, string strGoodsName, int intReinstatedDepartMentId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Goods_classify_pubid", SqlDbType.Int),
new SqlParameter("@Supply_units_id", SqlDbType.Int),
new SqlParameter("@Goods_rademark_id", SqlDbType.Int),
new SqlParameter("@Goods_code", SqlDbType.Char),
new SqlParameter("@Goods_name", SqlDbType.Char),
new SqlParameter("@Department_id", SqlDbType.Int)
};
sql[0].Value = "SelectGoodsOfReinstatedDepartMentByVariedTerm";
sql[1].Value = intGoodsClassify;
sql[2].Value = intSupplyUnits;
sql[3].Value = intGoodsRadeMark;
sql[4].Value = strGoodsCode;
sql[5].Value = strGoodsName;
sql[6].Value = intReinstatedDepartMentId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 新增部门调整单
public DataTable InsertSalesDepartmentAdjustment(string strRecordNumber, int intNewDepartMent,
int intReinstatedDepartment, DateTime dtmStartoverTime, int intMakeBills, string dtmMakeBills)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Record_number", SqlDbType.Char),
new SqlParameter("@New_department_id", SqlDbType.Int),
new SqlParameter("@Reinstated_department_id", SqlDbType.Int),
new SqlParameter("@Plan_start_time", SqlDbType.DateTime),
new SqlParameter("@Registrant_id", SqlDbType.Int),
new SqlParameter("@Register_time", SqlDbType.DateTime)
};
sql[0].Value = "InsertSalesDepartmentAdjustment";
sql[1].Value = strRecordNumber;
sql[2].Value = intNewDepartMent;
sql[3].Value = intReinstatedDepartment;
sql[4].Value = dtmStartoverTime;
sql[5].Value = intMakeBills;
sql[6].Value = dtmMakeBills;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 新增部门调整明细
public void InsertSalesDepartmentAdjustDetail(int intSalesDepartmentAdjustmentid, int intGoodsId,
decimal decStockQuantity)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Sales_department_adjust_id", SqlDbType.Int),
new SqlParameter("@Goods_id", SqlDbType.Int),
new SqlParameter("@Stock_quantity", SqlDbType.Decimal)
};
sql[0].Value = "InsertSalesDepartmentAdjustDetail";
sql[1].Value = intSalesDepartmentAdjustmentid;
sql[2].Value = intGoodsId;
sql[3].Value = decStockQuantity;
_myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 查询销售部门调整单
//查询全部未审核调整单
public DataTable GetAllSalesDepartment1()
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char)
};
sql[0].Value = "GetAllSalesDepartment1";
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
//查询全部未启动调整单
public DataTable GetAllSalesDepartment2()
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char)
};
sql[0].Value = "GetAllSalesDepartment2";
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 删除部门调整、调整明细
public int DeleteSalesDepartmentAdjustDetail(int intSalesDepartmentAdjustmentId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Sales_department_adjustment_id", SqlDbType.Int)
};
sql[0].Value = "DeleteSalesDepartmentAdjustDetail";
sql[1].Value = intSalesDepartmentAdjustmentId;
return _myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#region 删除部门调整
public int DeleteSalesDepartmentAdjustment(int intSalesDepartmentAdjustmentId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Sales_department_adjustment_id", SqlDbType.Int)
};
sql[0].Value = "DeleteSalesDepartmentAdjustment";
sql[1].Value = intSalesDepartmentAdjustmentId;
return _myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#endregion
#region 绑定部门调整明细
public DataTable BindingSalesDepartmentAdjustDetail(int intSalesDepartmentAdjustmentId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Sales_department_adjustment_id", SqlDbType.Int)
};
sql[0].Value = "BindingSalesDepartmentAdjustDetail";
sql[1].Value = intSalesDepartmentAdjustmentId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 修改销售部门调整单
public int UpdateSalesDepartmentAdjustment(string strRecordNumber, int intNewDepartMent,
int intReinstatedDepartment, DateTime dtmStartoverTime, int intMakeBills, DateTime dtmMakeBills,
int inttSalesDepartmentAdjustmentId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Record_number", SqlDbType.Char),
new SqlParameter("@New_department_id", SqlDbType.Int),
new SqlParameter("@Reinstated_department_id", SqlDbType.Int),
new SqlParameter("@Plan_start_time", SqlDbType.DateTime),
new SqlParameter("@Registrant_id", SqlDbType.Int),
new SqlParameter("@Register_time", SqlDbType.DateTime),
new SqlParameter("@Sales_department_adjustment_id", SqlDbType.Int)
};
sql[0].Value = "UpdateSalesDepartmentAdjustment";
sql[1].Value = strRecordNumber;
sql[2].Value = intNewDepartMent;
sql[3].Value = intReinstatedDepartment;
sql[4].Value = dtmStartoverTime;
sql[5].Value = intMakeBills;
sql[6].Value = dtmMakeBills;
sql[7].Value = inttSalesDepartmentAdjustmentId;
return _myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 获取销售部门调整单
public DataTable GetSalesDepartmentAdjustment(int intSalesDepartmentAdjustmentId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Sales_department_adjustment_id", SqlDbType.Int)
};
sql[0].Value = "GetSalesDepartmentAdjustment";
sql[1].Value = intSalesDepartmentAdjustmentId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 审核-销售部门调整
public int AuditingSalesDepartment(int intSalesDepartment, int intAuditor, DateTime dtmAuditorTime)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Sales_department_adjustment_id", SqlDbType.Int),
new SqlParameter("@Auditor_id", SqlDbType.Char),
new SqlParameter("@Review_time", SqlDbType.Char)
};
sql[0].Value = "AuditingSalesDepartment";
sql[1].Value = intSalesDepartment;
sql[2].Value = intAuditor;
sql[3].Value = dtmAuditorTime;
return _myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 启动
public int StartoverSalesDepartment(int intSalesDepartment, int intStartover, DateTime dtmStartoverTime)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Sales_department_adjustment_id", SqlDbType.Int),
new SqlParameter("@Startover_people_id", SqlDbType.Char),
new SqlParameter("@Startover_time", SqlDbType.Char)
};
sql[0].Value = "StartoverSalesDepartment";
sql[1].Value = intSalesDepartment;
sql[2].Value = intStartover;
sql[3].Value = dtmStartoverTime;
return _myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 不定条件查询销售部门
public DataTable SelectSalesDepartmetAjustmentWithTrends(
string strRecordNumber,
int intConsignmentDepartment,
int intNewDepartment,
DateTime dtmStart,
DateTime dtmEnd,
bool bolAuditingDeny,
bool bolStartoverDeny)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Record_number", SqlDbType.Char),
new SqlParameter("@Reinstated_department_id", SqlDbType.Int),
new SqlParameter("@New_department_id", SqlDbType.Int),
new SqlParameter("@dtmStart", SqlDbType.DateTime),
new SqlParameter("@dtmEnd", SqlDbType.DateTime),
new SqlParameter("@Review_deny", SqlDbType.Bit),
new SqlParameter("@Rtartover", SqlDbType.Bit)
};
sql[0].Value = "SelectSalesDepartmetAjustmentWithTrends";
sql[1].Value = strRecordNumber;
sql[2].Value = intConsignmentDepartment;
sql[3].Value = intNewDepartment;
sql[4].Value = dtmStart;
sql[5].Value = dtmEnd;
sql[6].Value = bolAuditingDeny;
sql[7].Value = bolStartoverDeny;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 保存修改-配送批次
public int UpdatePatch(int intPatchId, int quantity)
{
SqlParameter[] sql =
{
new SqlParameter("@NB",SqlDbType.Char),
new SqlParameter("@Delivery_quantity",SqlDbType.Int),
new SqlParameter("@Patch_id",SqlDbType.Int)
};
object[] param = { "UpdatePatch", quantity, intPatchId };
for (int i = 0; i < sql.Length; i++)
{
sql[i].Value = param[i];
}
return _myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 获取销售部门调整单明细
public DataTable GetSalesDepartmentDetails(int intSalesDepartment)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Sales_department_adjustment_id", SqlDbType.Int)
};
sql[0].Value = "GetSalesDepartmentDetails";
sql[1].Value = intSalesDepartment;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 不定条件查询销售部门调整单
public DataTable GetSalesDepartmentMessager1(int intSalesDepartment)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Sales_department_adjustment_id", SqlDbType.Int)
};
sql[0].Value = "GetSalesDepartmentMessager1";
sql[1].Value = intSalesDepartment;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
public DataTable GetSalesDepartmentMessager2(int intSalesDepartment)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Sales_department_adjustment_id", SqlDbType.Int)
};
sql[0].Value = "GetSalesDepartmentMessager2";
sql[1].Value = intSalesDepartment;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
public DataTable GetSalesDepartmentMessager3(int intSalesDepartment)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Sales_department_adjustment_id", SqlDbType.Int)
};
sql[0].Value = "GetSalesDepartmentMessager3";
sql[1].Value = intSalesDepartment;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 自动生成批次
public void AutoCreatePatch(int patchId, int quantum, int allotDetailId, int stockId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@PatchNumber_id", SqlDbType.Int),
new SqlParameter("@Detail_pubid", SqlDbType.Int),
new SqlParameter("@Delivery_quantity", SqlDbType.Int),
new SqlParameter("@Stock_id",SqlDbType.Int)
};
sql[0].Value = "AutoCreatePatch";
sql[1].Value = patchId;
sql[2].Value = allotDetailId;
sql[3].Value = quantum;
sql[4].Value = stockId;
_myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 查询配货批次信息
public DataTable SelectPatchMessage(int detailId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Detail_pubid", SqlDbType.Int)
};
sql[0].Value = "SelectPatchMessage";
sql[1].Value = detailId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 检查批次号
public DataTable CheckPatchNumber(int goodsId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Goods_id", SqlDbType.Int)
};
sql[0].Value = "CheckPatchNumber";
sql[1].Value = goodsId;
return _myAal.DAL_SelectDB_Par("Update_Day_Max_Count", sql);
}
#endregion
#region 新增批次记录
public DataTable AddPatchDayMaxCount(int goodsId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Goods_id", SqlDbType.Int)
};
sql[0].Value = "AddPatchDayMaxCount";
sql[1].Value = goodsId;
return _myAal.DAL_SelectDB_Par("Update_Day_Max_Count", sql);
}
#endregion
#region 根据调拨明细id查询调拨批次
public DataTable SelectAllotPatchIdByAllotDetailId(int detailIds)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.Char),
new SqlParameter("@Detail_pubid", SqlDbType.Int)
};
sql[0].Value = "SelectAllotPatchIdByAllotDetailId";
sql[1].Value = detailIds;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 获取销售部门调整表by销售部门调整
public DataTable GetSalesDepartmentById(int intSalesDepartment)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.NChar),
new SqlParameter("@Sales_department_adjust_id", SqlDbType.Int)
};
sql[0].Value = "GetSalesDepartmentById";
sql[1].Value = intSalesDepartment;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 启动销售部门调整
public void StartoverSalesDepartments(int reinId, int goodId, int newId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.NChar),
new SqlParameter("@Department_id", SqlDbType.Int),
new SqlParameter("@Goods_id", SqlDbType.Int),
new SqlParameter("@New_department_id", SqlDbType.Int)
};
sql[0].Value = "StartoverSalesDepartments";
sql[1].Value = reinId;
sql[2].Value = goodId;
sql[3].Value = newId;
_myAal.DAL_OPTableDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 查询同一部门同一商品的库存
public DataTable GetStockQuantity(int palceId, int goodsId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.NChar),
new SqlParameter("@Repertory_place_id", SqlDbType.Int),
new SqlParameter("@Goods_id", SqlDbType.Int)
};
sql[0].Value = "GetStockQuantity";
sql[1].Value = palceId;
sql[2].Value = goodsId;
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
#region 检查部门销售单审核否
public DataTable CheckShenHeFou(int inttSalesDepartmentAdjustmentId)
{
SqlParameter[] sql =
{
new SqlParameter("@NB", SqlDbType.NChar),
new SqlParameter("@Sales_department_adjustment_id", SqlDbType.Int)
};
object[] param = { "CheckShenHeFou", inttSalesDepartmentAdjustmentId };
for (int i = 0; i < sql.Length; i++)
{
sql[i].Value = param[i];
}
return _myAal.DAL_SelectDB_Par("Inventory_Control_Allot", sql);
}
#endregion
2.7、Sql-存储过程(公共):
ALTER PROCEDURE [dbo].[Update_Day_Max_Count]
@NB char(100)='',@RiQi date='1900-01-01',@Odd_numbers_id int=0,@Day_max_quantity int=0,
@Patch_number nchar(16)='',@Day_max_quantity_id int=0,@Goods_id int=0,@sql varchar(max)=''
AS
BEGIN
if(@NB='GetSalesDepartmentDayMaxQuantity') --获取销售部门调整日最大单数
begin
SELECT Day_max_count
FROM Day_max_quantity
where Day_max_quantity_id=2
end
if(@NB='GetDayMaxQuantity1') --获取调拨日最大数
begin
SELECT Day_max_count
FROM Day_max_quantity
where Day_max_quantity_id=4
end
if(@NB='CheckPatchNumber') --查询商品配送批次
begin
SELECT Day_max_quantity_id, Day_max_count
FROM Day_max_quantity
WHERE Odd_numbers_id = 1 AND Goods_id = @Goods_id
end
if(@NB='AddPatchDayMaxCount') --新增商品配送批次日最大数
begin
begin tran
INSERT INTO Day_max_quantity
(Odd_numbers_id, Goods_id, Day_max_count)
VALUES (1,@Goods_id,0)
select @@IDENTITY
commit tran
end
if(@NB='SavePatchNumber') --保存调拨批号
begin
INSERT INTO PatchNumber
(Patch_number, Day_max_quantity_id)
VALUES (@Patch_number,@Day_max_quantity_id)
select @@IDENTITY
end
if(@NB='SelectDayMaxCountOfPatc') --查询修改配货批次日最大数
begin
begin tran
set @sql='
SELECT Day_max_count, Odd_numbers_id, Goods_id
FROM Day_max_quantity
where Odd_numbers_id=1'
if (@Goods_id<>0) set @sql=@sql+' and Goods_id='+rtrim(@Goods_id)+''
exec(@sql)
set @sql='set nocount on
UPDATE Day_max_quantity
SET Day_max_count = Day_max_count + 1
where Odd_numbers_id=1'
if (@Goods_id<>0) set @sql=@sql+' and Goods_id='+rtrim(@Goods_id)+''
exec(@sql)
commit tran
end
if(@NB='GetDayMaxCount') --获取日最大单数
begin
SELECT Day_max_count
FROM Day_max_quantity
where Day_max_quantity_id=@Day_max_quantity_id
end
if(@NB='GetOddNumbers') --获取记录编号/批次号前缀
begin
SELECT Odd_numbers_id,Suffix_number,(rtrim(Prefix)+CONVERT(varchar(12),GETDATE(),112))as Prefixs
FROM Odd_numbers
WHERE (Odd_numbers_id = @Odd_numbers_id)
end
if(@NB='SelectJiLuBianMa')
begin
SELECT Day_max_count
FROM Day_max_quantity
where Odd_numbers_id=4
end
if(@NB='GetDayMaxQuantity')
begin
SELECT Day_max_count, Odd_numbers_id
FROM Day_max_quantity
where Day_max_quantity_id=@Day_max_quantity_id
end
if(@NB='GetOddNumbers') --获取批次号前缀
begin
SELECT Odd_numbers_id,Suffix_number,(rtrim(Prefix)+CONVERT(varchar(12),GETDATE(),112))as Prefixs
FROM Odd_numbers
WHERE (Odd_numbers_id = @Odd_numbers_id)
end
END
2.8、sql存储过程:
ALTER PROCEDURE [dbo].[Inventory_Control_Allot]
@NB char(100)='',@Department_id int=0,@Blur_content char(50)='',@Goods_id int=0,@Allot_id int=0, @Consignment_department_id int=0,
@Shipper_id int=0, @Receiving_department_id int=0, @Receiver_id int=0, @Record_number char(16)='', @Make_bills_time datetime='',
@Execute_time datetime='', @Transactor_id int=0, @Make_bills_id int=0, @Remarks char(30)='',@Allot_detail_id int=0,
@Shipments_quantity char(10)='',@Repertory_place_send_id int=0,@Repertory_place_harvest_id int=0,@Review_deny bit=false,@Repertory_place_id int=0,
@Stock_id int=0,@Check bit=false,@Department_Farid int=0,@Stock_quantity int=0,@dtmStart datetime='',@dtmEnd datetime='',
@Odd_numbers_id int=0,@PatchNumber_id int=0,@Detail_pubid int =0,@Delivery_quantity decimal=0,@Attribute_gather_id int=0,@Attribute_gather_detail_id int=0,
@sql varchar(max)='',@Goods_classify_pubid int=0,@Supply_units_id int=0,@Goods_rademark_id int=0,@Goods_code nchar(20)='',
@Goods_name nchar(20)='',@Reinstated_department_id int=0,@New_department_id int=0,@Plan_start_time datetime='',@Receipts_status_pubid int=0,@Registrant_id int=0,
@Register_time datetime='',@Startover_people_id int=0,@Startover_time datetime='',@Sales_department_adjust_id int=0,@Rtartover bit=false,
@Sales_department_adjustment_id int=0,@Auditor_id int=0,@Review_time datetime='',@Patch_id int=0
AS
BEGIN
if(@NB='GetAllDepartment') --获取全部部门信息
begin
SELECT Department.*
FROM Department
end
if(@NB='CheckPatch') --检查批次
begin
SELECT Patch_id
FROM Patch
where Detail_pubid=@Detail_pubid
end
if(@NB='UpdatePatch') --修改配送批次
begin
UPDATE Patch
SET Delivery_quantity =@Delivery_quantity
where Patch_id=@Patch_id
end
if(@NB='UpdateAllotToreView') --修改调拨单为已审核
begin
UPDATE Allot
SET Review_deny ='true'
where Allot_id=@Allot_id
end
if(@NB='UpdateStockQuantity') --修改库存/修改调拨明细为已审核
begin
UPDATE Stock
SET Stock_quantity =Stock_quantity-@Stock_quantity
WHERE (Stock_id = @Stock_id)
end
if(@NB='SelectAllotDetailByAllotId_reView') --查询调拨明细信息及库存-审核
begin
SELECT Allot_detail.Shipments_quantity, Stock.Stock_quantity, Goods.Goods_name, Allot_detail.Allot_detail_id, Stock.Stock_id
FROM Allot_detail INNER JOIN
Stock ON Allot_detail.Repertory_place_send_id = Stock.Repertory_place_id AND Allot_detail.Goods_id = Stock.Goods_id INNER JOIN
Goods ON Stock.Goods_id = Goods.Goods_id
WHERE (Allot_detail.Allot_id = @Allot_id)
end
if(@NB='SelectAllotPatchIdByAllotDetailId') --根据调拨明细id查询调拨批次
begin
SELECT Patch_id, PatchNumber_id
FROM Patch
WHERE (Detail_pubid = @Detail_pubid)
end
if(@NB='SelectPatchMessage') --查询批次信息
begin
SELECT Patch.PatchNumber_id, Patch.Detail_pubid, Patch.Delivery_quantity, Allot_detail.Allot_detail_id, PatchNumber.Patch_number, Goods.Goods_name, Allot_detail.Repertory_place_send_id,
Goods.Purchase_bid * (CAST(Goods.Input_ratio AS decimal(18, 5)) / 100 + 1) AS InputTax, Goods.Purchase_bid, Patch.Patch_id, Allot_detail.Goods_id, Patch.Stock_id, Stock.Stock_quantity
FROM Patch INNER JOIN
PatchNumber ON Patch.PatchNumber_id = PatchNumber.PatchNumber_id INNER JOIN
Allot_detail ON Patch.Detail_pubid = Allot_detail.Allot_detail_id INNER JOIN
Goods ON Allot_detail.Goods_id = Goods.Goods_id INNER JOIN
Stock ON Patch.Stock_id = Stock.Stock_id
WHERE (Patch.Detail_pubid = @Detail_pubid)
end
if(@NB='GetStockQuantity') --根据库存地点id和商品id查询库存
begin
SELECT Stock.Stock_quantity, Stock.Stock_id, Goods.Purchase_bid * (CAST(Goods.Input_ratio AS decimal(18, 5)) / 100 + 1) AS InputTax,
Goods.Purchase_bid
FROM Stock INNER JOIN
Goods ON Stock.Goods_id = Goods.Goods_id
WHERE (Stock.Repertory_place_id = @Repertory_place_id) AND (Stock.Goods_id = @Goods_id)
end
if(@NB='AutoCreatePatch') --自动生成配货批次
begin
INSERT INTO Patch
(PatchNumber_id, Detail_pubid, Delivery_quantity, Type_of_patch, Stock_id)
VALUES (@PatchNumber_id,@Detail_pubid,@Delivery_quantity, 144,@Stock_id)
end
if(@NB='GetAllotDetail') --根据调拨单id查询调拨明细
begin
SELECT Allot_detail.Shipments_quantity, Repertory_place.Repertory_place_name AS RepertoryPlaceSend, Repertory_place_1.Repertory_place_name AS RepertoryPlaceHarvest, Goods.Goods_id,
Goods.Goods_code, Goods.Goods_bar_code, Goods.Vender_bar_code_deny, Goods.Goods_name, Goods.Goods_abbreviation, Goods.Plncode_PLN, Goods.Copy_record_deny,
Goods.Producing_area_id, Goods.Art_No, Goods.Format_model, Goods.Goods_colours, Goods.Goods_rademark_id, Goods.Input_ratio, Goods.Output_ratio, Goods.Count_scale,
Goods.Quality_content, Goods.Permit_decimal_deny, Goods.Register_time, Goods.Review_time, Goods.Update_Time, Goods.Retail_unit_price, Goods.Purchase_bid, Goods.Self_fix_goods_deny,
Goods.Mini_fix_goods_deny, Allot_detail.Allot_detail_id, Allot_detail.Shipments_quantity * Goods.Quality_content AS Amount, Goods.Purchase_bid * (CAST(Goods.Input_ratio AS decimal(18, 5))
/ 100 + 1) AS InputTax, CONVERT(varchar(20), Goods.Register_time, 120) AS RegisterTime, CONVERT(varchar(20), Goods.Review_time, 120) AS ReviewTime, CONVERT(varchar(20),
Goods.Update_Time, 120) AS UpdateTime, Goods_rademark.Goods_rademark_name, Attribute_gather_detail.Attribute_gather_detail_name AS Goods_Tab, Agreement.Contract_number,
Manufacturer.Manufacturer_name, Goods_producing_area.Producing_area_name, Attribute_gather_detail_1.Attribute_gather_detail_name AS Unit_of_measurement,
Attribute_gather_detail_2.Attribute_gather_detail_name AS Use_target, Enterprise_name.Chinese, Attribute_gather_detail_3.Attribute_gather_detail_name AS Goods_classify,
Attribute_gather_detail_4.Attribute_gather_detail_name AS Quality_guarantee_period, Attribute_gather_detail_5.Attribute_gather_detail_name AS Quality_grade,
Manage_season.Manage_season_name, Attribute_gather_detail_6.Attribute_gather_detail_id AS Product_status, Eemployee.Employee_name AS Registrant,
Eemployee_1.Employee_name AS Review, Eemployee_2.Employee_name AS Update_person
FROM Eemployee INNER JOIN
Repertory_place INNER JOIN
Allot_detail INNER JOIN
Goods ON Allot_detail.Goods_id = Goods.Goods_id ON Repertory_place.Repertory_place_id = Allot_detail.Repertory_place_send_id INNER JOIN
Repertory_place AS Repertory_place_1 ON Allot_detail.Repertory_place_harvest_id = Repertory_place_1.Repertory_place_id INNER JOIN
Goods_rademark ON Goods.Goods_rademark_id = Goods_rademark.Goods_rademark_id INNER JOIN
Attribute_gather_detail ON Goods.Goods_tab_pubid = Attribute_gather_detail.Attribute_gather_detail_id INNER JOIN
Agreement ON Goods.Agreement_id = Agreement.Agreement_id INNER JOIN
Manufacturer ON Goods.Manufacturer_id = Manufacturer.Manufacturer_id INNER JOIN
Goods_producing_area ON Goods.Producing_area_id = Goods_producing_area.Producing_area_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_1 ON Goods.Unit_of_measurement_pubid = Attribute_gather_detail_1.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_2 ON Goods.Use_target_pubid = Attribute_gather_detail_2.Attribute_gather_detail_id INNER JOIN
Supply_units ON Agreement.Supply_units_id = Supply_units.Supply_units_id INNER JOIN
Enterprise_name ON Supply_units.Enterprise_name_id = Enterprise_name.Enterprise_name_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_3 ON Goods.Goods_classify_pubid = Attribute_gather_detail_3.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_4 ON Goods.Quality_guarantee_period_pubid = Attribute_gather_detail_4.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_5 ON Goods.Quality_grade_pubid = Attribute_gather_detail_5.Attribute_gather_detail_id INNER JOIN
Manage_season ON Goods.Manage_season_id = Manage_season.Manage_season_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_6 ON Goods.Product_status_pubid = Attribute_gather_detail_6.Attribute_gather_detail_id ON
Eemployee.Employee_id = Goods.Registrant_id INNER JOIN
Eemployee AS Eemployee_1 ON Goods.Auditor_id = Eemployee_1.Employee_id INNER JOIN
Eemployee AS Eemployee_2 ON Goods.Update_person_id = Eemployee_2.Employee_id WHERE (Allot_detail.Allot_id = @Allot_id)
end
if(@NB='GetAllot') --根据调拨单id查询调拨单
begin
SELECT Department.Department_name AS ConsignmentDepartment, Department_1.Department_name AS ReceivingDepartment,
Allot.Record_number,Allot.Remarks,
CONVERT(varchar(20), Allot.Make_bills_time, 120) AS Make_bills_time,
CONVERT(varchar(20), Allot.Execute_time, 120) AS Execute_time, --日期转换
(rtrim(Eemployee_1.Employee_name)+'('+rtrim(Eemployee_1.Employee_number)+')')as Receiver, --字符串拼接
(RTRIM(Eemployee_3.Employee_name)+'('+RTRIM(Eemployee_3.Employee_number)+')') as Make_bills,
(rtrim(Eemployee.Employee_name)+'('+rtrim(Eemployee.Employee_number)+')')as Shipper,
(RTRIM(Eemployee_2.Employee_name)+'('+RTRIM(Eemployee_2.Employee_number)+')') as Transactor
FROM Department INNER JOIN
Allot ON Department.Department_id = Allot.Consignment_department_id INNER JOIN
Department AS Department_1 ON Allot.Receiving_department_id = Department_1.Department_id INNER JOIN
Eemployee ON Allot.Shipper_id = Eemployee.Employee_id INNER JOIN
Eemployee AS Eemployee_1 ON Allot.Receiver_id = Eemployee_1.Employee_id INNER JOIN
Eemployee AS Eemployee_2 ON Allot.Transactor_id = Eemployee_2.Employee_id INNER JOIN
Eemployee AS Eemployee_3 ON Allot.Make_bills_id = Eemployee_3.Employee_id
WHERE (Allot.Allot_id = @Allot_id)
end
if(@NB='SelectAllAllotOfReview') --多条件动态查询调拨单
begin
set @sql='SELECT Department_1.Department_name AS ConsignmentDepartment, Department.Department_name AS ReceivingDepartment, Allot.Allot_id, Allot.Record_number, Allot.Remarks, Allot.Review_deny,
Eemployee.Employee_name AS Shipper, CONVERT(nchar(20), Allot.Make_bills_time, 120) AS Make_bills_time, CONVERT(nchar(20), Allot.Execute_time, 120) AS Execute_time,
Eemployee_1.Employee_name AS Make_bills, Eemployee_2.Employee_name AS Receiver, Eemployee_3.Employee_name AS Transactor
FROM Allot INNER JOIN
Department AS Department_1 ON Allot.Consignment_department_id = Department_1.Department_id INNER JOIN
Department ON Allot.Receiving_department_id = Department.Department_id INNER JOIN
Eemployee ON Allot.Shipper_id = Eemployee.Employee_id INNER JOIN
Eemployee AS Eemployee_1 ON Allot.Make_bills_id = Eemployee_1.Employee_id INNER JOIN
Eemployee AS Eemployee_2 ON Allot.Receiver_id = Eemployee_2.Employee_id INNER JOIN
Eemployee AS Eemployee_3 ON Allot.Transactor_id = Eemployee_3.Employee_id
WHERE Allot.Review_deny = '''+rtrim(cast(@Check as NCHAR(4)))+''' AND Allot.Execute_time BETWEEN '''+rtrim(cast(@dtmStart as date)) +''' AND
'''+ rtrim(cast(@dtmEnd as date))+''''
if(@Record_number<>'')set @sql=@sql+' AND (Allot.Record_number LIKE '' % '+rtrim(@Record_number)+' % '''
if(@Consignment_department_id<>0)set @sql=@sql+' and Consignment_department_id='+rtrim(cast(@Consignment_department_id as int))+''
if(@Receiving_department_id<>0) set @sql=@sql+' and Receiving_department_id='+rtrim(cast(@Receiving_department_id as int))+''
if(@Remarks<>'') set @sql=@sql+' and Remarks like ''%'+rtrim(cast(@Remarks as NCHAR(30)))+'%'''
exec (@sql)
end
if(@NB='GetConsignmentDepartmentAll') --获取所以部门信息绑定发货部门tree
begin
SELECT Department_id , Department_Farid , Department_code , Department_name
FROM Department
end
if(@NB='GetReceivingDepartmentById') --查询同级部门
begin
SELECT Department_id as id , Department_code as code, Department_name as name
FROM Department
where Department.Department_Farid=(SELECT Department_Farid
FROM Department where Department_id=@Department_id)
end
if(@NB='DepartmentStaffSelectbyDepartmentId') --根据部门ID查找部门员工
begin
SELECT Eemployee.Employee_id, Eemployee.Employee_number, Eemployee.Employee_name,(case Sex when 'true' then '男' else'女' end) as sex, Duties.Duties_name,
Department.Department_name
FROM Eemployee INNER JOIN
Department ON Eemployee.Department_id = Department.Department_id INNER JOIN
Duties ON Eemployee.Duties_id = Duties.Duties_id
where Eemployee.Department_id=@Department_id
end
if(@NB='GoodsAllSelect' and @Goods_id=0 ) --获取所有商品信息
begin
SELECT Attribute_gather_detail_1.Attribute_gather_detail_name AS Use_target, Attribute_gather_detail_2.Attribute_gather_detail_name AS Goods_classify,
Attribute_gather_detail_4.Attribute_gather_detail_name AS Quality_grade, Attribute_gather_detail_6.Attribute_gather_detail_name AS Goods_tab,
Attribute_gather_detail_5.Attribute_gather_detail_name AS Product_status, Attribute_gather_detail_3.Attribute_gather_detail_name AS Quality_guarantee_period, Agreement.Contract_number,
Manufacturer.Manufacturer_name, Enterprise_name.Chinese, Goods_producing_area.Producing_area_name, Goods_rademark.Goods_rademark_name, Manage_season.Manage_season_name,
Eemployee_1.Employee_name AS Registrant, Eemployee_2.Employee_name AS Review, Eemployee.Employee_name AS Update_person,
Attribute_gather_detail.Attribute_gather_detail_name AS Unit_of_measurement, Goods.Goods_id, Goods.Goods_code, Goods.Goods_bar_code, Goods.Vender_bar_code_deny, Goods.Goods_name,
Goods.Goods_abbreviation, Goods.Plncode_PLN, Goods.Copy_record_deny, Goods.Goods_tab_pubid, Goods.Agreement_id, Goods.Manufacturer_id, Goods.Producing_area_id, Goods.Art_No,
Goods.Format_model, Goods.Unit_of_measurement_pubid, Goods.Use_target_pubid, Goods.Goods_colours, Goods.Goods_rademark_id, Goods.Goods_classify_pubid,
Goods.Quality_guarantee_period_pubid, Goods.Input_ratio, Goods.Output_ratio, Goods.Count_scale, Goods.Quality_content, Goods.Quality_grade_pubid, Goods.Manage_season_id,
Goods.Permit_decimal_deny, Goods.Product_status_pubid, Goods.Retail_unit_price, Goods.Purchase_bid, Goods.Self_fix_goods_deny, Goods.Mini_fix_goods_deny
FROM Eemployee INNER JOIN
Enterprise_name INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_5 INNER JOIN
Goods INNER JOIN
Attribute_gather_detail ON Goods.Unit_of_measurement_pubid = Attribute_gather_detail.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_1 ON Goods.Use_target_pubid = Attribute_gather_detail_1.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_2 ON Goods.Goods_classify_pubid = Attribute_gather_detail_2.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_3 ON Goods.Quality_guarantee_period_pubid = Attribute_gather_detail_3.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_4 ON Goods.Quality_grade_pubid = Attribute_gather_detail_4.Attribute_gather_detail_id ON
Attribute_gather_detail_5.Attribute_gather_detail_id = Goods.Product_status_pubid INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_6 ON Goods.Goods_tab_pubid = Attribute_gather_detail_6.Attribute_gather_detail_id INNER JOIN
Agreement ON Goods.Agreement_id = Agreement.Agreement_id INNER JOIN
Supply_units ON Agreement.Supply_units_id = Supply_units.Supply_units_id ON Enterprise_name.Enterprise_name_id = Supply_units.Enterprise_name_id INNER JOIN
Manufacturer ON Goods.Manufacturer_id = Manufacturer.Manufacturer_id INNER JOIN
Goods_producing_area ON Goods.Producing_area_id = Goods_producing_area.Producing_area_id INNER JOIN
Manage_season ON Goods.Manage_season_id = Manage_season.Manage_season_id ON Eemployee.Employee_id = Goods.Registrant_id INNER JOIN
Eemployee AS Eemployee_1 ON Goods.Auditor_id = Eemployee_1.Employee_id INNER JOIN
Eemployee AS Eemployee_2 ON Goods.Update_person_id = Eemployee_2.Employee_id INNER JOIN
Goods_rademark ON Goods.Goods_rademark_id = Goods_rademark.Goods_rademark_id
end
else
if(@NB='GoodsAllSelect' and @Goods_id>0) --查询商品信息by商品id
begin
SELECT Attribute_gather_detail_1.Attribute_gather_detail_name AS Use_target, Attribute_gather_detail_2.Attribute_gather_detail_name AS Goods_classify,
Attribute_gather_detail_4.Attribute_gather_detail_name AS Quality_grade, Attribute_gather_detail_6.Attribute_gather_detail_name AS Goods_tab,
Attribute_gather_detail_5.Attribute_gather_detail_name AS Product_status, Attribute_gather_detail_3.Attribute_gather_detail_name AS Quality_guarantee_period, Agreement.Contract_number,
Manufacturer.Manufacturer_name, Enterprise_name.Chinese, Goods_producing_area.Producing_area_name, Goods_rademark.Goods_rademark_name, Manage_season.Manage_season_name,
Eemployee_1.Employee_name AS Registrant, Eemployee_2.Employee_name AS Review, Eemployee.Employee_name AS Update_person,
Attribute_gather_detail.Attribute_gather_detail_name AS Unit_of_measurement, Goods.Goods_id, Goods.Goods_code, Goods.Goods_bar_code, Goods.Vender_bar_code_deny, Goods.Goods_name,
Goods.Goods_abbreviation, Goods.Plncode_PLN, Goods.Copy_record_deny, Goods.Goods_tab_pubid, Goods.Agreement_id, Goods.Manufacturer_id, Goods.Producing_area_id, Goods.Art_No,
Goods.Format_model, Goods.Unit_of_measurement_pubid, Goods.Use_target_pubid, Goods.Goods_colours, Goods.Goods_rademark_id, Goods.Goods_classify_pubid,
Goods.Quality_guarantee_period_pubid, Goods.Input_ratio, Goods.Output_ratio, Goods.Count_scale, Goods.Quality_content, Goods.Quality_grade_pubid, Goods.Manage_season_id,
Goods.Permit_decimal_deny, Goods.Product_status_pubid, Goods.Retail_unit_price, Goods.Purchase_bid, Goods.Self_fix_goods_deny, Goods.Mini_fix_goods_deny
FROM Eemployee INNER JOIN
Enterprise_name INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_5 INNER JOIN
Goods INNER JOIN
Attribute_gather_detail ON Goods.Unit_of_measurement_pubid = Attribute_gather_detail.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_1 ON Goods.Use_target_pubid = Attribute_gather_detail_1.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_2 ON Goods.Goods_classify_pubid = Attribute_gather_detail_2.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_3 ON Goods.Quality_guarantee_period_pubid = Attribute_gather_detail_3.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_4 ON Goods.Quality_grade_pubid = Attribute_gather_detail_4.Attribute_gather_detail_id ON
Attribute_gather_detail_5.Attribute_gather_detail_id = Goods.Product_status_pubid INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_6 ON Goods.Goods_tab_pubid = Attribute_gather_detail_6.Attribute_gather_detail_id INNER JOIN
Agreement ON Goods.Agreement_id = Agreement.Agreement_id INNER JOIN
Supply_units ON Agreement.Supply_units_id = Supply_units.Supply_units_id ON Enterprise_name.Enterprise_name_id = Supply_units.Enterprise_name_id INNER JOIN
Manufacturer ON Goods.Manufacturer_id = Manufacturer.Manufacturer_id INNER JOIN
Goods_producing_area ON Goods.Producing_area_id = Goods_producing_area.Producing_area_id INNER JOIN
Manage_season ON Goods.Manage_season_id = Manage_season.Manage_season_id ON Eemployee.Employee_id = Goods.Registrant_id INNER JOIN
Eemployee AS Eemployee_1 ON Goods.Auditor_id = Eemployee_1.Employee_id INNER JOIN
Eemployee AS Eemployee_2 ON Goods.Update_person_id = Eemployee_2.Employee_id INNER JOIN
Goods_rademark ON Goods.Goods_rademark_id = Goods_rademark.Goods_rademark_id
WHERE (Goods.Goods_id = @Goods_id)
end
if(@NB='BlurSelect') --模糊查询商品信息
begin
SELECT Attribute_gather_detail_1.Attribute_gather_detail_name AS Use_target, Attribute_gather_detail_2.Attribute_gather_detail_name AS Goods_classify, Attribute_gather_detail_4.Attribute_gather_detail_name AS Quality_grade,
Attribute_gather_detail_6.Attribute_gather_detail_name AS Goods_tab, Attribute_gather_detail_5.Attribute_gather_detail_name AS Product_status, Attribute_gather_detail_3.Attribute_gather_detail_name AS Quality_guarantee_period, Agreement.Contract_number,
Manufacturer.Manufacturer_name, Enterprise_name.Chinese, Goods_producing_area.Producing_area_name, Goods_rademark.Goods_rademark_name, Manage_season.Manage_season_name, Eemployee_1.Employee_name AS Registrant,
Eemployee_2.Employee_name AS Review, Eemployee.Employee_name AS Update_person, Attribute_gather_detail.Attribute_gather_detail_name AS Unit_of_measurement, Goods.*
FROM Eemployee INNER JOIN
Enterprise_name INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_5 INNER JOIN
Goods INNER JOIN
Attribute_gather_detail ON Goods.Unit_of_measurement_pubid = Attribute_gather_detail.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_1 ON Goods.Use_target_pubid = Attribute_gather_detail_1.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_2 ON Goods.Goods_classify_pubid = Attribute_gather_detail_2.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_3 ON Goods.Quality_guarantee_period_pubid = Attribute_gather_detail_3.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_4 ON Goods.Quality_grade_pubid = Attribute_gather_detail_4.Attribute_gather_detail_id ON Attribute_gather_detail_5.Attribute_gather_detail_id = Goods.Product_status_pubid INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_6 ON Goods.Goods_tab_pubid = Attribute_gather_detail_6.Attribute_gather_detail_id INNER JOIN
Agreement ON Goods.Agreement_id = Agreement.Agreement_id INNER JOIN
Supply_units ON Agreement.Supply_units_id = Supply_units.Supply_units_id ON Enterprise_name.Enterprise_name_id = Supply_units.Enterprise_name_id INNER JOIN
Manufacturer ON Goods.Manufacturer_id = Manufacturer.Manufacturer_id INNER JOIN
Goods_producing_area ON Goods.Producing_area_id = Goods_producing_area.Producing_area_id INNER JOIN
Manage_season ON Goods.Manage_season_id = Manage_season.Manage_season_id ON Eemployee.Employee_id = Goods.Registrant_id INNER JOIN
Eemployee AS Eemployee_1 ON Goods.Auditor_id = Eemployee_1.Employee_id INNER JOIN
Eemployee AS Eemployee_2 ON Goods.Update_person_id = Eemployee_2.Employee_id INNER JOIN
Goods_rademark ON Goods.Goods_rademark_id = Goods_rademark.Goods_rademark_id
where Goods.Goods_name like '%'+ rtrim(ltrim(@Blur_content)) +'%' or
Goods.Goods_code like '%'+ rtrim(ltrim(@Blur_content)) +'%' or
Goods.Goods_bar_code like '%'+ rtrim(ltrim(@Blur_content)) +'%'
end
if(@NB='RepertoryPlaceSendSelect') --获取发货仓库地点
begin
SELECT Repertory_place_id as Repertory_place_send_id, Repertory_place_name as Repertory_place_send
FROM Repertory_place
where Department_id=@Department_id
end
if(@NB='RepertoryPlaceHarvestSelect') --获取收货仓库地点
begin
SELECT Repertory_place_id as Repertory_place_harvest_id, Repertory_place_name as Repertory_place_harvest
FROM Repertory_place
where Department_id=@Department_id
end
if(@NB='GetEmployeeByDepartId') --获取制单人/执行人
begin
SELECT Employee_id , Employee_name
FROM Eemployee
where Department_id=@Department_id
end
if(@NB='SaveAllot') --保存调拨单
begin
begin tran
INSERT INTO Allot
(Consignment_department_id, Shipper_id, Receiving_department_id, Receiver_id, Record_number, Make_bills_time, Execute_time, Transactor_id, Make_bills_id, Remarks, Review_deny)
VALUES (@Consignment_department_id,@Shipper_id,@Receiving_department_id,@Receiver_id,@Record_number,@Make_bills_time,@Execute_time,@Transactor_id,@Make_bills_id,@Remarks, 'false')
select @@IDENTITY
SELECT Day_max_count
FROM Day_max_quantity
UPDATE Day_max_quantity
SET Day_max_count = Day_max_count + 1
where Day_max_quantity_id=4
commit tran
end
if(@NB='SaveAllotDetail') --保存调拨单明细
begin
INSERT INTO Allot_detail
(Allot_id, Goods_id, Shipments_quantity, Repertory_place_send_id, Repertory_place_harvest_id)
VALUES (@Allot_id,@Goods_id,@Shipments_quantity,@Repertory_place_send_id,@Repertory_place_harvest_id)
end
if(@NB='GetAllRecordNumberOfAllot') --查询所有调拨单编号
begin
if(@Blur_content='')--获取全部调拨单编号
SELECT Allot.Allot_id, Department_1.Department_name AS Consignment_department, Department.Department_name AS Receiving_department, Allot.Remarks, Allot.Consignment_department_id, Allot.Receiving_department_id, Allot.Record_number
FROM Allot INNER JOIN
Department AS Department_1 ON Allot.Consignment_department_id = Department_1.Department_id INNER JOIN
Department ON Allot.Receiving_department_id = Department.Department_id
where Allot.Review_deny='false'
else --模糊查询调拨单编号
SELECT Allot.Allot_id, Department_1.Department_name AS Consignment_department, Department.Department_name AS Receiving_department, Allot.Remarks, Allot.Consignment_department_id, Allot.Receiving_department_id, Allot.Record_number
FROM Allot INNER JOIN
Department AS Department_1 ON Allot.Consignment_department_id = Department_1.Department_id INNER JOIN
Department ON Allot.Receiving_department_id = Department.Department_id
where Allot.Record_number like '%' + rtrim(ltrim(@Blur_content)) +'%' and Allot.Review_deny='false'
end
if(@NB='SelectAllotByAllotId') --查询调拨单by调拨id
begin
SELECT Allot.Consignment_department_id, Allot.Shipper_id, Allot.Receiving_department_id, Allot.Receiver_id, Department.Department_name AS FaHuoBuMen,
Department_1.Department_name AS ShouHuoBuMen, Eemployee.Employee_name AS Shipper, Eemployee_1.Employee_name AS Receiver, Allot.Remarks,convert(nchar(20),Make_bills_time,120) as Make_bills_time,
convert(nchar(20),Execute_time,120) as Execute_time, Allot.Transactor_id, Allot.Make_bills_id, Allot.Record_number, Department.Department_Farid, Department_1.Department_code
FROM Department INNER JOIN
Allot ON Department.Department_id = Allot.Consignment_department_id INNER JOIN
Department AS Department_1 ON Allot.Receiving_department_id = Department_1.Department_id INNER JOIN
Eemployee ON Allot.Shipper_id = Eemployee.Employee_id INNER JOIN
Eemployee AS Eemployee_1 ON Allot.Receiver_id = Eemployee_1.Employee_id
WHERE (Allot.Allot_id = @Allot_id)
end
if(@NB='SelectAllotDetailByAllotId') --查询调拨单明细by调拨单id
begin
SELECT Attribute_gather_detail.Attribute_gather_detail_name AS Goods_tab, Attribute_gather_detail_1.Attribute_gather_detail_name AS Unit_of_measurement,
Attribute_gather_detail_2.Attribute_gather_detail_name AS Use_target, Attribute_gather_detail_3.Attribute_gather_detail_name AS Goods_classify,
Attribute_gather_detail_4.Attribute_gather_detail_name AS Quality_guarantee_period, Attribute_gather_detail_5.Attribute_gather_detail_name AS Quality_grade,
Attribute_gather_detail_6.Attribute_gather_detail_name AS Product_status, Eemployee.Employee_name AS Registrant, Eemployee_1.Employee_name AS Review,
Eemployee_2.Employee_name AS Update_person, Manufacturer.Manufacturer_name, Enterprise_name.Chinese, Manage_season.Manage_season_name,
Goods_producing_area.Producing_area_name, Goods_rademark.Goods_rademark_name, Allot_detail.Allot_id, Allot_detail.Goods_id, Allot_detail.Shipments_quantity,
Allot_detail.Repertory_place_send_id, Allot_detail.Repertory_place_harvest_id, Goods.Goods_id AS Expr1, Goods.Goods_code, Goods.Goods_bar_code, Goods.Vender_bar_code_deny,
Goods.Goods_name, Goods.Goods_abbreviation, Goods.Plncode_PLN, Goods.Copy_record_deny, Goods.Goods_tab_pubid, Goods.Agreement_id, Goods.Manufacturer_id,
Goods.Producing_area_id, Goods.Art_No, Goods.Format_model, Goods.Unit_of_measurement_pubid, Goods.Use_target_pubid, Goods.Goods_colours, Goods.Goods_rademark_id,
Goods.Goods_classify_pubid, Goods.Quality_guarantee_period_pubid, Goods.Input_ratio, Goods.Output_ratio, Goods.Count_scale, Goods.Quality_content, Goods.Quality_grade_pubid,
Goods.Manage_season_id, Goods.Permit_decimal_deny, Goods.Product_status_pubid, Goods.Registrant_id, Goods.Register_time, Goods.Auditor_id, Goods.Review_time,
Goods.Update_person_id, Goods.Update_Time, Goods.Retail_unit_price, Goods.Purchase_bid, Goods.Self_fix_goods_deny, Goods.Mini_fix_goods_deny,
Allot_detail.Repertory_place_send_id AS Expr2, Allot_detail.Repertory_place_harvest_id AS Expr3, Repertory_place.Repertory_place_name AS Repertory_place_send,
Repertory_place_1.Repertory_place_name AS Repertory_place_harvest, Agreement.Contract_number, Allot_detail.Allot_detail_id
FROM Repertory_place INNER JOIN
Eemployee INNER JOIN
Enterprise_name INNER JOIN
Supply_units ON Enterprise_name.Enterprise_name_id = Supply_units.Enterprise_name_id INNER JOIN
Attribute_gather_detail INNER JOIN
Allot_detail INNER JOIN
Goods ON Allot_detail.Goods_id = Goods.Goods_id ON Attribute_gather_detail.Attribute_gather_detail_id = Goods.Goods_tab_pubid INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_1 ON Goods.Unit_of_measurement_pubid = Attribute_gather_detail_1.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_2 ON Goods.Use_target_pubid = Attribute_gather_detail_2.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_3 ON Goods.Goods_classify_pubid = Attribute_gather_detail_3.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_4 ON Goods.Quality_guarantee_period_pubid = Attribute_gather_detail_4.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_5 ON Goods.Quality_grade_pubid = Attribute_gather_detail_5.Attribute_gather_detail_id INNER JOIN
Attribute_gather_detail AS Attribute_gather_detail_6 ON Goods.Product_status_pubid = Attribute_gather_detail_6.Attribute_gather_detail_id INNER JOIN
Agreement ON Goods.Agreement_id = Agreement.Agreement_id ON Supply_units.Supply_units_id = Agreement.Supply_units_id INNER JOIN
Manufacturer ON Goods.Manufacturer_id = Manufacturer.Manufacturer_id INNER JOIN
Goods_producing_area ON Goods.Producing_area_id = Goods_producing_area.Producing_area_id INNER JOIN
Goods_rademark ON Goods.Goods_rademark_id = Goods_rademark.Goods_rademark_id INNER JOIN
Manage_season ON Goods.Manage_season_id = Manage_season.Manage_season_id ON Eemployee.Employee_id = Goods.Registrant_id INNER JOIN
Eemployee AS Eemployee_1 ON Goods.Auditor_id = Eemployee_1.Employee_id INNER JOIN
Eemployee AS Eemployee_2 ON Goods.Update_person_id = Eemployee_2.Employee_id ON Repertory_place.Repertory_place_id = Allot_detail.Repertory_place_send_id INNER JOIN
Repertory_place AS Repertory_place_1 ON Allot_detail.Repertory_place_harvest_id = Repertory_place_1.Repertory_place_id
WHERE (Allot_detail.Allot_id = @Allot_id)
end
if(@NB='DeleteAllotByAllotId') --删除调拨单/调拨单明细
begin
begin tran
--删除调拨批次
DELETE FROM Patch
where Detail_pubid in (SELECT Allot_detail_id
FROM Allot_detail where Allot_id=@Allot_id)
--删除调拨明细
DELETE FROM Allot_detail
where Allot_id=@Allot_id
--删除调拨单
DELETE FROM Allot
where Allot_id=@Allot_id
commit tran
end
if(@NB='UpdateDiaoBoDanMingXi') --修改调拨明细
begin
UPDATE Allot_detail
SET Shipments_quantity =@Shipments_quantity, Repertory_place_send_id =@Repertory_place_send_id, Repertory_place_harvest_id =@Repertory_place_harvest_id
where Allot_detail_id=@Allot_detail_id
end
if(@NB='UpdateDiaoBoDan') --修改调拨单/修改调拨单明细/新增调拨单明细
begin
UPDATE Allot
SET Consignment_department_id =@Consignment_department_id, Shipper_id =@Shipper_id, Receiving_department_id =@Receiving_department_id,
Receiver_id =@Receiver_id, Make_bills_time =@Make_bills_time, Execute_time =@Execute_time, Make_bills_id =@Make_bills_id,
Remarks =ltrim(@Remarks), Review_deny =@Review_deny, Transactor_id =@Transactor_id
where Allot_id=@Allot_id
end
if(@NB='CheckStockOfConsignmentDepartment') --获取库存
begin
SELECT Stock_quantity , Stock_toplimit , Stock_floor , Stock_id
FROM Stock
WHERE (Repertory_place_id = @Repertory_place_id) AND (Goods_id= @Goods_id)
order by Stock_id
end
if(@NB='CheckStockOfReceivingDepartment') --检查收货部门库存
begin
SELECT Stock_quantity , Stock_toplimit , Stock_id
FROM Stock
WHERE (Repertory_place_id= @Repertory_place_id) AND (Goods_id= @Goods_id)
end
if(@NB='DeleteDiaoBoMingXiByDiaoBoMingXiId') --根据调拨明细id删除调拨明细
begin
DELETE FROM Allot_detail
where Allot_detail_id=@Allot_detail_id
end
if(@NB='StartoverSalesDepartment') --手工启动销售部门调整
begin
UPDATE Sales_department_adjustment
SET Rtartover = 'true', Startover_time =@Startover_time, Startover_people_id =@Startover_people_id
where (Sales_department_adjustment_id = @Sales_department_adjustment_id)
end
if(@NB='AuditingSalesDepartment') --审核销售部门调整
begin
UPDATE Sales_department_adjustment
SET Review_deny = 'true', Auditor_id =@Auditor_id, Review_time =@Review_time
WHERE (Sales_department_adjustment_id = @Sales_department_adjustment_id)
end
if(@NB='GetSalesDepartmentAdjustment') --获取销售部门调整单
begin
SELECT Sales_department_adjustment_id, Reinstated_department_id, New_department_id,CONVERT(nchar(20),Plan_start_time,120)as Plan_start_time, Receipts_status_pubid, Registrant_id,CONVERT(nchar(20),Register_time,120) as Register_time, Record_number
FROM Sales_department_adjustment
where Sales_department_adjustment_id=@Sales_department_adjustment_id
end
if(@NB='UpdateSalesDepartmentAdjustment') --修改销售部门调整单
begin
UPDATE Sales_department_adjustment
SET Reinstated_department_id = @Reinstated_department_id, New_department_id = @New_department_id, Plan_start_time = @Plan_start_time, Receipts_status_pubid = 1,
Registrant_id = @Registrant_id, Register_time = @Register_time, Record_number = @Record_number, Review_deny = 'false'
where Sales_department_adjustment_id=@Sales_department_adjustment_id
end
if(@NB='BindingSalesDepartmentAdjustDetail') --绑定部门调整明细
begin
SELECT Goods.Goods_code, Goods.Goods_bar_code, Goods.Goods_name, Goods.Goods_abbreviation, Goods.Art_No, Goods.Unit_of_measurement_pubid AS Unit_of_measurement,
Goods.Retail_unit_price, Goods.Format_model, Sales_department_adjust_detail.Stock_quantity, Sales_department_adjust_detail.Sales_department_adjust_detail_id,
Sales_department_adjust_detail.Goods_id, Sales_department_adjust_detail.Sales_department_adjust_id
FROM Sales_department_adjust_detail INNER JOIN
Goods ON Sales_department_adjust_detail.Goods_id = Goods.Goods_id
WHERE (Sales_department_adjust_detail.Sales_department_adjust_id = @Sales_department_adjustment_id)
end
if(@NB='DeleteSalesDepartmentAdjustment') --删除部门调整
begin
DELETE FROM Sales_department_adjustment
where Sales_department_adjustment.Sales_department_adjustment_id=@Sales_department_adjustment_id
end
if(@NB='DeleteSalesDepartmentAdjustDetail') -- 删除部门调整、调整明细
begin
DELETE FROM Sales_department_adjust_detail
where Sales_department_adjust_detail.Sales_department_adjust_id=@Sales_department_adjustment_id
end
if(@NB='GetAllSalesDepartment1') --查询全部未审核调整单
begin
SELECT Sales_department_adjustment.Sales_department_adjustment_id, Sales_department_adjustment.Reinstated_department_id, Sales_department_adjustment.New_department_id,
CONVERT(varchar(20), Sales_department_adjustment.Plan_start_time, 120) AS Plan_start_time, Sales_department_adjustment.Record_number, Department_1.Department_id,
Department.Department_id AS Expr1, Department_1.Department_name AS Reinstated_department, Department.Department_name AS New_department, Sales_department_adjustment.Registrant_id,
CONVERT(varchar(20), Sales_department_adjustment.Register_time, 120) AS Register_time, Sales_department_adjustment.Auditor_id,CONVERT(varchar(20), Sales_department_adjustment.Review_time, 120) as Review_time
FROM Sales_department_adjustment INNER JOIN
Department AS Department_1 ON Sales_department_adjustment.Reinstated_department_id = Department_1.Department_id INNER JOIN
Department ON Sales_department_adjustment.New_department_id = Department.Department_id
WHERE (Sales_department_adjustment.Rtartover = 'false')and (Sales_department_adjustment.Review_deny='false')
end
if(@NB='GetAllSalesDepartment2') --查询全部未启动调整单
begin
SELECT Sales_department_adjustment.Sales_department_adjustment_id, Sales_department_adjustment.Reinstated_department_id, Sales_department_adjustment.New_department_id,
CONVERT(varchar(20), Sales_department_adjustment.Plan_start_time, 120) AS Plan_start_time, Sales_department_adjustment.Record_number, Department_1.Department_id,
Department.Department_id AS Expr1, Department_1.Department_name AS Reinstated_department, Department.Department_name AS New_department, Sales_department_adjustment.Registrant_id,
CONVERT(varchar(20), Sales_department_adjustment.Register_time, 120) AS Register_time, Sales_department_adjustment.Auditor_id,CONVERT(varchar(20), Sales_department_adjustment.Review_time, 120) as Review_time
FROM Sales_department_adjustment INNER JOIN
Department AS Department_1 ON Sales_department_adjustment.Reinstated_department_id = Department_1.Department_id INNER JOIN
Department ON Sales_department_adjustment.New_department_id = Department.Department_id
WHERE (Sales_department_adjustment.Rtartover = 'false')and (Sales_department_adjustment.Review_deny='true')
end
if(@NB='InsertSalesDepartmentAdjustDetail') --新增销售部门调整单明细
begin
INSERT INTO Sales_department_adjust_detail
(Sales_department_adjust_id, Goods_id,Stock_quantity)
VALUES (@Sales_department_adjust_id,@Goods_id,@Stock_quantity)
end
if(@NB='InsertSalesDepartmentAdjustment') --新增部门调整单 --新增销售部门调整单
begin
begin tran
INSERT INTO Sales_department_adjustment
(Reinstated_department_id, New_department_id, Plan_start_time, Receipts_status_pubid, Registrant_id, Register_time, Record_number, Review_deny, Rtartover)
VALUES (@Reinstated_department_id,@New_department_id,@Plan_start_time, 1,@Registrant_id,@Register_time,@Record_number, 'false','false')
select @@IDENTITY
UPDATE Day_max_quantity
SET Day_max_count =Day_max_count+1
where Day_max_quantity_id=2
commit tran
end
if(@NB='SelectGoodsOfReinstatedDepartMentByVariedTerm') --不定条件查询部门调整商品
begin
set @sql='SELECT Goods.Goods_id, Goods.Goods_code, Goods.Goods_bar_code, Goods.Goods_name, Goods.Goods_abbreviation, Goods.Art_No, Goods.Unit_of_measurement_pubid AS Unit_of_measurement,
Goods.Retail_unit_price, Goods.Format_model, sum(Stock.Stock_quantity) as Stock_quantity
FROM Agreement INNER JOIN
Goods ON Agreement.Agreement_id = Goods.Agreement_id INNER JOIN
Goods_SalesDepartment ON Goods.Goods_id = Goods_SalesDepartment.Goods_id INNER JOIN
Stock ON Goods.Goods_id = Stock.Goods_id INNER JOIN
Repertory_place ON Stock.Repertory_place_id = Repertory_place.Repertory_place_id AND Goods_SalesDepartment.Department_id = Repertory_place.Department_id
WHERE Goods_SalesDepartment.Department_id ='+rtrim(cast(@Department_id as int))+' and Stock.Stock_quantity<>0 '
if(@Goods_classify_pubid <>0) set @sql=@sql+ ' and Goods_classify_pubid ='+rtrim(cast(@Goods_classify_pubid as int))+''
if(@Supply_units_id<>0) set @sql=@sql+' and Agreement.Supply_units_id='+RTRIM(CAST(@Supply_units_id as int))+''
if (@Goods_rademark_id<>0) set @sql=@sql+' and Goods.Goods_rademark_id='+RTRIM(CAST(@Goods_rademark_id as int))+''
if(@Goods_code<>'') set @sql=@sql+' and Goods.Goods_code like''%'+RTRIM(CAST(@Goods_code as NCHAR(20)))+'%'''
if(@Goods_name<>'') set @sql=@sql+' and Goods.Goods_name like''%'+RTRIM(CAST(@Goods_name as NCHAR(20)))+'%'''
set @sql=@sql+'group by Goods.Goods_id, Goods.Goods_code, Goods.Goods_bar_code, Goods.Goods_name, Goods.Goods_abbreviation, Goods.Art_No, Goods.Unit_of_measurement_pubid ,
Goods.Retail_unit_price, Goods.Format_model'
exec(@sql)
end
if(@NB='GetGoodsRademarkByReinstatedDepartMentGoods') --根据原部门绑定部门所拥有商品的商标
begin
SELECT Goods_rademark.Goods_rademark_name as name, Goods.Goods_rademark_id as id
FROM Goods_SalesDepartment INNER JOIN
Goods ON Goods_SalesDepartment.Goods_id = Goods.Goods_id INNER JOIN
Goods_rademark ON Goods.Goods_rademark_id = Goods_rademark.Goods_rademark_id
where Goods_SalesDepartment.Department_id=@Department_id
group by Goods_rademark.Goods_rademark_name, Goods.Goods_rademark_id
end
if(@NB='GetSupplyUnitsOfReinstatedDepartMentGoods') --根据部门查询往来单位
begin
SELECT Supply_units.Supply_units_id as id, Enterprise_name.Chinese as name
FROM Goods INNER JOIN
Agreement ON Goods.Agreement_id = Agreement.Agreement_id INNER JOIN
Supply_units ON Agreement.Supply_units_id = Supply_units.Supply_units_id INNER JOIN
Enterprise_name ON Supply_units.Enterprise_name_id = Enterprise_name.Enterprise_name_id
where Goods_id =
(SELECT Goods_id
FROM Goods_SalesDepartment
where Goods_SalesDepartment.Department_id = @Department_id and Goods_SalesDepartment.Goods_id=Goods.Goods_id and Goods.Agreement_id=Agreement.Agreement_id and
Agreement.Supply_units_id=Supply_units.Supply_units_id
group by Goods_SalesDepartment.Goods_id)
group by Supply_units.Supply_units_id, Enterprise_name.Chinese
end
if(@NB='GetGoodsClassifyByAttributeGatherDetailId') --根据商品分类id查询商品分类farid
begin
SELECT Attribute_gather_detail_farid AS farid, Attribute_gather_detail_name AS name, Attribute_code as code
FROM Attribute_gather_detail
where Attribute_gather_id=11 and Attribute_gather_detail_id=@Attribute_gather_detail_id
end
if(@NB='GetGoodsClassifyOfReinstatedDepartMent') --获取部门商品分类
begin
SELECT Attribute_gather_detail_1.Attribute_gather_detail_name AS text, Goods.Goods_classify_pubid as id
FROM Attribute_gather_detail AS Attribute_gather_detail_1 INNER JOIN
Goods_SalesDepartment INNER JOIN
Goods ON Goods_SalesDepartment.Goods_id = Goods.Goods_id ON Attribute_gather_detail_1.Attribute_gather_detail_id = Goods.Goods_classify_pubid
WHERE (Goods_SalesDepartment.Department_id = @Department_id)
GROUP BY Attribute_gather_detail_1.Attribute_gather_detail_name, Goods.Goods_classify_pubid
end
if(@NB='GetSalesDepartmentDetails') --获取销售部门调整单明细
begin
SELECT Sales_department_adjust_detail.Sales_department_adjust_detail_id, Sales_department_adjust_detail.Stock_quantity, Goods.Goods_id, Goods.Goods_code, Goods.Goods_bar_code,
Goods.Goods_name, Goods.Art_No, Goods.Retail_unit_price, Attribute_gather_detail.Attribute_gather_detail_name AS Unit_of_measurement, Goods.Goods_abbreviation,
Goods.Format_model,Goods.Purchase_bid * (CAST(Goods.Input_ratio AS decimal(18, 5))/ 100 + 1) AS InputTax
FROM Sales_department_adjust_detail INNER JOIN
Goods ON Sales_department_adjust_detail.Goods_id = Goods.Goods_id INNER JOIN
Attribute_gather_detail ON Goods.Unit_of_measurement_pubid = Attribute_gather_detail.Attribute_gather_detail_id
WHERE (Sales_department_adjust_detail.Sales_department_adjust_id = @Sales_department_adjustment_id)
end
if(@NB='GetSalesDepartmentMessager1') --查询既审核又启动的销售部门调整
begin
SELECT Department.Department_name AS Reinstated_department, Department_1.Department_name AS New_department, Sales_department_adjustment.Record_number,
Sales_department_adjustment.Plan_start_time, Eemployee.Employee_name AS Startover_people, Eemployee_1.Employee_name AS Auditor,
Eemployee_2.Employee_name AS Registrant,
convert(char(20),Sales_department_adjustment.Startover_time,120) as Startover_time,
convert(char(20),Sales_department_adjustment.Review_time,120) as Review_time,
convert(char(20),Sales_department_adjustment.Register_time,120) as Register_time
FROM Sales_department_adjustment INNER JOIN
Department ON Sales_department_adjustment.Reinstated_department_id = Department.Department_id INNER JOIN
Department AS Department_1 ON Sales_department_adjustment.New_department_id = Department_1.Department_id INNER JOIN
Eemployee ON Sales_department_adjustment.Startover_people_id = Eemployee.Employee_id INNER JOIN
Eemployee AS Eemployee_1 ON Sales_department_adjustment.Auditor_id = Eemployee_1.Employee_id INNER JOIN
Eemployee AS Eemployee_2 ON Sales_department_adjustment.Registrant_id = Eemployee_2.Employee_id
where Sales_department_adjustment.Sales_department_adjustment_id=@Sales_department_adjustment_id
end
if(@NB='GetSalesDepartmentMessager2') --查询只审核,未启动的销售部门调整
begin
SELECT Department.Department_name AS Reinstated_department, Department_1.Department_name AS New_department, Sales_department_adjustment.Record_number,
Sales_department_adjustment.Plan_start_time, Eemployee_1.Employee_name AS Auditor, Eemployee_2.Employee_name AS Registrant,
CONVERT(char(20), Sales_department_adjustment.Review_time, 120) AS Review_time, CONVERT(char(20),
Sales_department_adjustment.Register_time, 120) AS Register_time
FROM Sales_department_adjustment INNER JOIN
Department ON Sales_department_adjustment.Reinstated_department_id = Department.Department_id INNER JOIN
Department AS Department_1 ON Sales_department_adjustment.New_department_id = Department_1.Department_id INNER JOIN
Eemployee AS Eemployee_1 ON Sales_department_adjustment.Auditor_id = Eemployee_1.Employee_id INNER JOIN
Eemployee AS Eemployee_2 ON Sales_department_adjustment.Registrant_id = Eemployee_2.Employee_id
WHERE (Sales_department_adjustment.Sales_department_adjustment_id = @Sales_department_adjustment_id)
end
if(@NB='GetSalesDepartmentMessager3') --查询既没启动有每审核销售部门调整单
begin
SELECT Department.Department_name AS Reinstated_department, Department_1.Department_name AS New_department, Sales_department_adjustment.Record_number,
Sales_department_adjustment.Plan_start_time, Eemployee_2.Employee_name AS Registrant,
CONVERT(char(20), Sales_department_adjustment.Register_time, 120) AS Register_time
FROM Sales_department_adjustment INNER JOIN
Department ON Sales_department_adjustment.Reinstated_department_id = Department.Department_id INNER JOIN
Department AS Department_1 ON Sales_department_adjustment.New_department_id = Department_1.Department_id INNER JOIN
Eemployee AS Eemployee_2 ON Sales_department_adjustment.Registrant_id = Eemployee_2.Employee_id
where Sales_department_adjustment.Sales_department_adjustment_id=@Sales_department_adjustment_id
end
if(@NB='SelectSalesDepartmetAjustmentWithTrends') --不定条件查询销部门调整
begin
set @sql='SELECT Sales_department_adjustment.Record_number, Department.Department_name AS Reinstated_department, Department_1.Department_name AS New_department,
Sales_department_adjustment.New_department_id, CONVERT(char(20), Sales_department_adjustment.Plan_start_time, 120) AS Plan_start_time,
Sales_department_adjustment.Sales_department_adjustment_id, Sales_department_adjustment.Review_deny, Sales_department_adjustment.Rtartover
FROM Sales_department_adjustment INNER JOIN
Department ON Sales_department_adjustment.Reinstated_department_id = Department.Department_id INNER JOIN
Department AS Department_1 ON Sales_department_adjustment.New_department_id = Department_1.Department_id
where Sales_department_adjustment.Review_deny='+rtrim(@Review_deny)+' and Sales_department_adjustment.Rtartover='+rtrim(@Rtartover)+'
and Sales_department_adjustment.Plan_start_time between'''+rtrim(cast(@dtmStart as date)) +'''and'''+ rtrim(cast(@dtmEnd as date))+''''
if(@Record_number<>'') set @sql=@sql+' and Sales_department_adjustment.Record_number like ''%'+rtrim(@Record_number)+'%'''
if(@Reinstated_department_id<>'') set @sql=@sql+' and Sales_department_adjustment.Reinstated_department_id='+RTRIM(@Reinstated_department_id)+''
if(@New_department_id<>'') set @sql=@sql+' and Sales_department_adjustment.New_department_id='+RTRIM(@New_department_id)+''
exec(@sql)
end
if(@NB='GetSalesDepartmentById') --获取销售部门调整表by销售部门调整id
begin
SELECT Sales_department_adjustment.Sales_department_adjustment_id, Sales_department_adjustment.Reinstated_department_id, Sales_department_adjustment.New_department_id,
Sales_department_adjust_detail.Goods_id, Sales_department_adjust_detail.Stock_quantity
FROM Sales_department_adjustment INNER JOIN
Sales_department_adjust_detail ON Sales_department_adjustment.Sales_department_adjustment_id = Sales_department_adjust_detail.Sales_department_adjust_id
WHERE (Sales_department_adjust_detail.Sales_department_adjust_id = @Sales_department_adjust_id)
end
if(@NB='CheckShenHeFou') --检查部门销售单审核否
begin
SELECT Review_deny
FROM Sales_department_adjustment
where Sales_department_adjustment_id=@Sales_department_adjustment_id
end
if(@NB='StartoverSalesDepartments') --启动销售部门调整
begin
begin tran
DELETE FROM Goods_SalesDepartment
where Department_id=@Department_id and Goods_id=@Goods_id
INSERT INTO Goods_SalesDepartment
(Department_id, Goods_id, YouXiaoFou)
VALUES (@New_department_id,@Goods_id,'true')
commit tran
end
if(@NB='GetSalesDepartmentBySalesDeparId') --根据销售部门调整id查询调整单是否已审核
begin
SELECT Review_deny
FROM Sales_department_adjustment
where Sales_department_adjustment_id=@Sales_department_adjustment_id
end
END
3、数据库设计:
用到的主要的表有:调拨表,调拨明细表,库存地点表,商品表,库存表,部门表,员工表:
表名 | 说明 | 类型 | 表编号 |
Allot | 调拨表 | 业务表 | 表1 |
Allot_detail | 调拨明细表 | 业务表 | 表2 |
Repertory_place | 库存地点表 | 基础表 | 表3 |
Stock | 库存表 | 基础表 | 表4 |
Goods | 商品表 | 基础表 | 表5 |
Department | 部门表 | 基础表 | 表6 |
Eemployee | 员工表 | 基础表 | 表7 |
列名 | 数据类型 | 主键/外键 | 说明 |
Allot_id | Int | 主键 |
|
Consignment_department_id | Int | 外键 | ‘部门表’,发货部门 |
Receiving_department_id | Int | 外键 | ‘部门表’,收货部门 |
Shipper_id | int | 外键 | ‘员工表’,发货人 |
Receiver_id | Int | 外键 | ‘员工表’,收货人 |
Make_bills_id | int | 外键 | ‘员工表’,制单人 |
Transactor_id | Int | 外键 | ‘员工表’,启动人 |
Make_bills_time | Datetime |
| 制单时间 |
Execute_time | Datetime |
| 启动时间 |
Record_number | Nchar(16) |
| 记录编号 |
Remarks | Nchar(30) |
| 备注 |
Review_deny | Bit |
| 审核否 |
列名 | 数据类型 | 主键/外键 | 说明 |
Allot_detail_id | Int | 主键 |
|
Allot_id | Int | 外键 | ‘调拨表’,调拨id |
Goods_id | Int | 外键 | ‘商品表’,商品id |
Shipments_quantity | Int |
| 调拨数量 |
Repertory_place_send_id | Int | 外键 | ‘库存地点表’,发货部门库存地点 |
Repertory_place_harvest_id | Int | 外键 | ‘库存地点表’,收货部门库存地点 |
列名 | 数据类型 | 主键/外键 | 说明 |
Repertory_place_id | Int | 主键 |
|
Responsible_person_id | Int | 外键 | ‘员工表’,仓管员 |
Department_id | Int | 外键 | ‘部门表’,部门id |
Repertory_place_name | nchar(10) |
| 仓库名称 |
Contact_way | nchar(10) |
| 联系方式 |
Address | nchar(10) |
| 仓库地点 |
Effective_deny | Bit |
| 有效否 |
Remarks | nchar(10) |
| 备注 |
Repertory_place_identifier | nchar(10) |
| 仓库代码 |
列名 | 数据类型 | 主键/外键 | 说明 |
Stock_id | Int | 主键 |
|
Repertory_place_id | Int | 外键 | ‘库存地点表’,库存地点 |
Goods_id | Int |
| ‘商品表’,商品 |
Stock_quantity | Int |
| 库存数量 |
Stock_toplimit | Int |
| 库存上限 |
Stock_floor | Int |
| 库存下限 |
Repertory_enter_detail_id | Int | 外键 | ‘进仓明细表’,进仓明细 |
列名 | 数据类型 | 主键/外键 | 说明 |
Department_id | Int | 主键 |
|
Department_Farid | Int | 外键 | ‘部门表’,部门id |
Department_code | nchar(10) |
| 部门代码 |
Department_name | nchar(10) |
| 部门名称 |
列名 | 数据类型 | 主键/外键 | 说明 |
Employee_id | Int | 主键 |
|
Employee_number | nchar(12) |
| 员工号 |
Employee_name | nchar(10) |
| 员工姓名 |
Department_id | int | 外键 | ‘部门表’,部门名称 |
ID_number | nchar(18) |
| 身份证号 |
Duties_id | int | 外键 | ‘职务表’,职务名称 |
Sex | bit |
| 性别 |
列名 | 数据类型 | 主键/外键 | 说明 |
Goods_id | Int | 主键 |
|
Goods_code | nchar(10) |
| 商品代码 |
Goods_bar_code | nchar(10) |
| 条码 |
Vender_bar_code_deny | Bit |
| 厂家条码否 |
Goods_name | nchar(10) |
| 商品名称 |
Goods_abbreviation | int | 外键 | 商品简称 |
Plncode_PLN | nchar(10) |
| PLN 码 |
Copy_record_deny | Bit |
| 复制记录否 |
Goods_tab_pubid | Int | 外键 | ‘属性明细表’,商品标记 |
Agreement_id | Int | 外键 | ‘合同表’,合同 |
Manufacturer_id | Int | 外键 | ‘商品厂家’,生产厂家 |
Producing_area_id | Int | 外键 | ‘产地表’,产地 |
Art_No | nchar(10) |
| 货号 |
Format_model | nchar(10) |
| 规格型号 |
Unit_of_measurement_pubid | Int | 外键 | ‘属性明细’,计量单位 |
Use_target_pubid | Int | 外键 | ‘属性明细’,使用对象 |
Goods_colours | nchar(10) |
| 商品花色 |
Goods_rademark_id | Int | 外键 | ‘商品商标表’,商品商标 |
Goods_classify_pubid | Int | 外键 | ‘属性明细’,商品分类 |
Quality_guarantee_period_pubid | Int | 外键 | ‘属性明细’,保质期 |
Input_ratio | decimal(18, 0) |
| 进项税率 |
Output_ratio | decimal(18, 0) |
| 销项税率 |
Count_scale | nchar(10) |
| 统计比例 |
Quality_content | Int |
| 质地含量 |
Quality_grade_pubid | Int | 外键 | 质量等级 |
Manage_season_id | Int | 外键 | ‘经营季节表’,经营季节 |
Permit_decimal_deny | Bit |
| 允许小数否 |
Product_status_pubid | Int | 外键 | ‘属性明细’,产品状态 |
Registrant_id | Int | 外键 | ‘员工表’,登记人 |
Register_time | Datetime |
| 登记时间 |
Auditor_id | Int | 外键 | ‘员工表’,审核人 |
Review_time | Datetime |
| 审核时间 |
Update_person_id | Int | 外键 | ‘员工表‘,更新人 |
Update_Time | Datetime |
| 更新时间 |
Retail_unit_price | Nchar(10) |
| 零售单价 |
Purchase_bid | nchar(10) |
| 采购进价 |
Self_fix_goods_deny | bit |
| 自订货否 |
Mini_fix_goods_deny | nchar(10) |
| 最小订货否 |
(注:销售部门调整与商品调拨共用AllotController控制器,BLL层,存储过程,所有业务公用ConvertHelperController,公共部分,以后业务不再重复附上。)
本教程仅供学习用,禁止用于商业用途