ERP打印入库单（四十）
需求描述：
此购进单的基本信息，购进单位，入库单位，入库时间……
此购进单批号，产品名称，生产企业，等基本信息。
实现能够循环加载打印。
本单金额小计，整单金额合计计算。
技术需求：
界面设计，循环加载数据
实现函数：根据产品编号查询产品生产企业
实现函数：根据产品查询产品规格
实现函数：根据产品查询产品单位
实现金额数字转换大写
金额大小写转换的类：
namespace CommTool
{
/// <summary>
/// 字符串处理相关
/// </summary>
public class StringHandler
{
/// <summary>
/// author:sunliyuan
/// sunliyuan:2011年12月4日
/// 转换人民币大小金额
/// </summary>
/// <param name="num">金额</param>
/// <returns>返回大写形式</returns>
public static string CmycurD(decimal num)
{
string str1 = "零壹贰叁肆伍陆柒捌玖"; //0-9所对应的汉字
string str2 = "万仟佰拾亿仟佰拾万仟佰拾元角分"; //数字位所对应的汉字
string str3 = ""; //从原num值中取出的值
string str4 = ""; //数字的字符串形式
string str5 = ""; //人民币大写金额形式
int i; //循环变量
int j; //num的值乘以100的字符串长度
string ch1 = ""; //数字的汉语读法
string ch2 = ""; //数字位的汉字读法
int nzero = 0; //用来计算连续的零值是几个
int temp; //从原num值中取出的值
num = Math.Round(Math.Abs(num), 2); //将num取绝对值并四舍五入取2位小数
str4 = ((long)(num * 100)).ToString(); //将num乘100并转换成字符串形式
j = str4.Length; //找出最高位
if (j > 15) { return "溢出"; }
str2 = str2.Substring(15 - j); //取出对应位数的str2的值。如：200.55,j为5所以str2=佰拾元角分
//循环取出每一位需要转换的值
for (i = 0; i < j; i++)
{
str3 = str4.Substring(i, 1); //取出需转换的某一位的值
temp = Convert.ToInt32(str3); //转换为数字
if (i != (j - 3) && i != (j - 7) && i != (j - 11) && i != (j - 15))
{
//当所取位数不为元、万、亿、万亿上的数字时
if (str3 == "0")
{
ch1 = "";
ch2 = "";
nzero = nzero + 1;
}
else
{
if (str3 != "0" && nzero != 0)
{
ch1 = "零" + str1.Substring(temp * 1, 1);
ch2 = str2.Substring(i, 1);
nzero = 0;
}
else
{
ch1 = str1.Substring(temp * 1, 1);
ch2 = str2.Substring(i, 1);
nzero = 0;
}
}
}
else
{
//该位是万亿，亿，万，元位等关键位
if (str3 != "0" && nzero != 0)
{
ch1 = "零" + str1.Substring(temp * 1, 1);
ch2 = str2.Substring(i, 1);
nzero = 0;
}
else
{
if (str3 != "0" && nzero == 0)
{
ch1 = str1.Substring(temp * 1, 1);
ch2 = str2.Substring(i, 1);
nzero = 0;
}
else
{
if (str3 == "0" && nzero >= 3)
{
ch1 = "";
ch2 = "";
nzero = nzero + 1;
}
else
{
if (j >= 11)
{
ch1 = "";
nzero = nzero + 1;
}
else
{
ch1 = "";
ch2 = str2.Substring(i, 1);
nzero = nzero + 1;
}
}
}
}
}
if (i == (j - 11) || i == (j - 3))
{
//如果该位是亿位或元位，则必须写上
ch2 = str2.Substring(i, 1);
}
str5 = str5 + ch1 + ch2;
if (i == j - 1 && str3 == "0")
{
//最后一位（分）为0时，加上“整”
str5 = str5 + '整';
}
}
if (num == 0)
{
str5 = "零元整";
}
return str5;
}
/// <summary>
/// author:sunliyuan
/// 2011年12月4日
/// 转换人民币大小金额 (一个重载，将字符串先转换成数字在调用CmycurD)
/// </summary>
/// <param name="num">用户输入的金额，字符串形式未转成decimal</param>
/// <returns></returns>
public static string CmycurD(string numstr)
{
try
{
decimal num = Convert.ToDecimal(numstr);
return CmycurD(num);
}
catch
{
return "非数字形式！";
}
}
}
}
根据产品编号查询产品的生产企业
-- Description: 根据产品编号查询产品的生产企业 -- ============================================= CREATE FUNCTION [dbo].[FN_getMadeEnterpriseByProID] ( @ProID INT ) RETURNS NVARCHAR(100) AS BEGIN DECLARE @MadeEnterprise NVARCHAR(100) SELECT @MadeEnterprise=MadeEnterprise FROM dbo.BiotbProduct WHERE ProID=@ProID RETURN @MadeEnterprise END
根据产品编号查询产品规格：
-- Description: 根据产品编号查询产品规格 -- ============================================= CREATE FUNCTION [dbo].[FN_getProSpecbyProID] ( @ProID INT ) RETURNS NVARCHAR(100) AS BEGIN -- Declare the return variable here DECLARE @Spec NVARCHAR(100) -- Add the T-SQL statements to compute the return value here SELECT @Spec=spec FROM BiotbProduct WHERE ProID=@ProID -- Return the result of the function RETURN @Spec END
根据产品编号查询产品单位：
-- Description: 根据产品编号查询产品单位 -- ============================================= CREATE FUNCTION [dbo].[FN_getProUnitbyProID] ( @proID INT ) RETURNS NVARCHAR(50) AS BEGIN -- Declare the return variable here DECLARE @Unit NVARCHAR(50) -- Add the T-SQL statements to compute the return value here SELECT @Unit=Unit FROM dbo.BiotbProduct WHERE ProID=@proID -- Return the result of the function RETURN @Unit END
构建查询打印的视图数据：
CREATE VIEW [dbo].[View_PurchaseInfoPrint] AS SELECT SendComName=dbo.getCompanyNameByCompanyID(SendComID), AppUserName=dbo.getUserNameByUserID(AppUserID), AuditingUser=dbo.getUserNameByUserID(AcceptUserid), stockUserName=dbo.getUserNameByUserID(Stockuserid), StockName=dbo.FN_getStockNameByStockID(StockID), StockDate=dbo.Fn_getSotckTimeByPurchaseID(PurchaseID), * FROM dbo.BioPurchaseAppInfo
CREATE VIEW [dbo].[View_PurchaseBatchInfoPrint]
AS
SELECT
ProName,
Spec=dbo.FN_getProSpecbyProID(ProID),
MadeEnterprise=dbo.FN_getMadeEnterpriseByProID(ProID),
Unit=dbo.FN_getProUnitbyProID(ProID),
ProCount,
ProPrice,
ProBatchPriceTotal=(ProPrice*realityProCount),
InvoicePrice,
PurchaseProID,
PurchaseID,
ProID,
makeDate,
batchNum,
expirationDate,
ProBatchID,
stockDate,
boxNum,
BatchProCount,
realityProCount
FROM
View_PurchaseProBatchInfo
根据仓库编号查询仓库名称：
-- Description: 根据仓库编号查询仓库名称 -- ============================================= CREATE FUNCTION [dbo].[FN_getStockNameByStockID] ( -- Add the parameters for the function here @stockID INT ) RETURNS NVARCHAR(100) AS BEGIN DECLARE @StockName NVARCHAR(100) SELECT @StockName=StockName FROM dbo.BioErpStockTable WHERE ID=@stockID RETURN @StockName END
打印的前端代码：
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="RuKuPrint.aspx.cs" Inherits="BioErpWeb.Print.RuKuPrint" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>入库（验收、通知）单打印</title>
<link href="../Styles/Print.css" rel="stylesheet" type="text/css" />
<script type="text/javascript">
function setTbConSame(control) {
var usernames = document.getElementsByName("username");
if (usernames.length != 0) {
for (var i = 0; i < usernames.length; i++) {
usernames[i].value = control.value;
}
}
}
</script>
</head>
<body>
<form id="form1" runat="server" style="margin:0; padding:0;" >
<table>
<%
//产品购进单基本信息（1条）
System.Data.DataSet ds = GetDataSet();
//产品批号信息（多条）
System.Data.DataSet ds1 = this.GetProBatchsDataSet();
int mypage = 0;
if ((ds1.Tables[0].Rows.Count) % 5 == 0)
{
mypage = (int)((ds1.Tables[0].Rows.Count) / 5);
}
else
{
mypage = ((int)((ds1.Tables[0].Rows.Count) / 5)) + 1;
}
decimal mon = 0;
for (int n = 0; n < ds1.Tables[0].Rows.Count; n++)
{
mon += Convert.ToDecimal(ds1.Tables[0].Rows[n]["ProBatchPriceTotal"].ToString().Trim());
}
//Convert.ToDecimal(mon);
int x = ds1.Tables[0].Rows.Count;
//绑定固定的联系人或者制单人名
for (int i = 0; i < x;)
{
%>
<tr>
<td>
<table style="height: 310px; width: 210mm;" align="left"
border="0" cellpadding="0" cellspacing="0">
<tr>
<td height="24mm" width="223mm">
<table border="0" cellpadding="0" cellspacing="0"
style="height: 20mm; width: 210mm;" align="left">
<tr>
<td colspan="6" style="font-size: larger; font-weight: bold"
align="center" class="style17">
<font size="4"><%=ds.Tables[0].Rows[0]["OrderCom"]%>入库（验收、通知）单</font></td>
</tr>
<tr>
<td align="right" width="75px" height="8mm" >
<font style="font-family: 宋体; font-size:12px;">发货单位：</font></td>
<td align="left" width="254px">
<font style="font-family: 宋体; font-size:12px;"><%=ds.Tables[0].Rows[0]["SendComName"]%></font>
</td>
<td align="right" width="75px" >
<font style="font-family: 宋体; font-size:12px;">入库方式：</font></td>
<td align="left" width="231px" >
<font style="font-family: 宋体; font-size:12px;"><%=ds.Tables[0].Rows[0]["sendType"]%></font></td>
<td align="right" width="75px" >
<font style="font-family: 宋体; font-size:12px;">系统单号：</font></td>
<td align="left" >
<font style="font-family: 宋体; font-size:12px;"><%=DateTime.Now.ToString("yyyyMMddhhmmss")+ds.Tables[0].Rows[0]["PurchaseID"]%></font></td>
</tr>
<tr>
<td align="right" height="8mm" >
<font style="font-family: 宋体; font-size:12px;">仓 库：</font></td>
<td align="left" width="254px" >
<%=ds.Tables[0].Rows[0]["StockName"]%></td>
<td align="right" >
<font style="font-family: 宋体; font-size:12px;">入库时间：</font></td>
<td align="left" width="231px">
<font style="font-family: 宋体; font-size:12px;"><%=Convert.ToDateTime(ds.Tables[0].Rows[0]["StockDate"]).ToString("yyyy-MM-dd")%></font></td>
<td align="right">
<font style="font-family: 宋体; font-size:12px;">自定义单号：</font></td>
<td align="left">
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td align="left" valign="top" height="43mm">
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td align="left" valign="top" height="43mm" class="style8">
<table align="left" border="0" cellpadding="0" cellspacing="0"
class="Prinaround">
<tr align="center" valign="middle" style="height: 6mm">
<td width="40px" class="Printright" height="6mm">
<font style="font-family: 宋体; font-size:12px;"> </font></td>
<td class="style16">
<font style="font-family: 宋体; font-size:12px;">商品名称</font></td>
<td class="style14">
<font style="font-family: 宋体; font-size:12px;">规 格</font></td>
<td class="style22">
<font style="font-family: 宋体; font-size:12px;">生产企业</font></td>
<td class="style23">
<font style="font-family: 宋体; font-size:12px;">单位</font></td>
<td class="style10">
<font style="font-family: 宋体; font-size:12px;">数量</font></td>
<td class="style28">
<font style="font-family: 宋体; font-size:12px;">单价</font></td>
<td class="style18">
<font style="font-family: 宋体; font-size:12px;">金额</font></td>
<td class="style29">
<font style="font-family: 宋体; font-size:12px;">生产日期</font></td>
<td class="style26">
<font style="font-family: 宋体; font-size:12px;">批号</font></td>
<td class="style27">
<font style="font-family: 宋体; font-size:12px;">有效期</font></td>
</tr>
<%
decimal currentmoney = 0;
for (int j = 0; j < 5 && i < x; j++, i++)
{
currentmoney += Convert.ToDecimal(ds1.Tables[0].Rows[i]["ProBatchPriceTotal"]);
%>
<tr align="center" valign="middle" style="height: 6mm">
<td class="Printright" height="6mm">
<table><tr><td> <font style="font-family: 宋体; font-size:12px;"> <%=i+1 %></font></td></tr></table></td>
<td class="style16">
<font style="font-family: 宋体; font-size:12px;"><span><%=ds1.Tables[0].Rows[i]["ProName"]%> </span></font></td>
<td class="style14">
<font style="font-family: 宋体; font-size:11px;"><span><%=ds1.Tables[0].Rows[i]["Spec"]%></span></font></td>
<td class="style22">
<font style="font-family: 宋体; font-size:11px;"><span><%=ds1.Tables[0].Rows[i]["MadeEnterprise"]%></span></font></td>
<td class="style23">
<font style="font-family: 宋体; font-size:11px;"><span><%=ds1.Tables[0].Rows[i]["Unit"]%></span></font></td>
<td class="style10">
<font style="font-family: 宋体; font-size:12px;"><span><%=ds1.Tables[0].Rows[i]["realityProCount"]%></span></font></td>
<td class="style28">
<font style="font-family: 宋体; font-size:12px;"><span><%=Convert.ToDecimal(ds1.Tables[0].Rows[i]["ProPrice"]).ToString("0.00")%></span></font></td>
<td class="style18">
<font style="font-family: 宋体; font-size:12px;"><span><%=Convert.ToDecimal(ds1.Tables[0].Rows[i]["ProBatchPriceTotal"]).ToString("0.00")%></span></font></td>
<td class="style29">
<font style="font-family: 宋体; font-size:12px;"><span><%=Convert.ToDateTime(ds1.Tables[0].Rows[i]["makeDate"]).ToString("yyyy-MM-dd")%></span></font></td>
<td class="style26">
<font style="font-family: 宋体; font-size:12px;"><span><%=ds1.Tables[0].Rows[i]["batchNum"]%></span></font></td>
<td class="style27">
<font style="font-family: 宋体; font-size:12px;"><span><%=Convert.ToDateTime(ds1.Tables[0].Rows[i]["expirationDate"]).ToString("yyyy-MM-dd")%></span></font></td>
</tr>
<%
}
%>
</table>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td height="18mm" valign="top">
<table border="0" cellpadding="0" cellspacing="0"
style="height: 21mm; width: 770px;">
<tr>
<td colspan="2" class="style20">
<font style="font-family:font-family: 宋体; font-size:12px;">本单入库金额小计：<%=CommTool.StringHandler.CmycurD(currentmoney)+"(￥"+currentmoney.ToString("0.00")+")" %></font></td>
<td colspan="5" class="style20" >
<font style="font-family: 宋体; font-size:12px;">整单入库金额合计：<%=CommTool.StringHandler.CmycurD(mon)+"(￥"+mon.ToString("0.00")+")"%></font></td>
</tr>
<tr>
<td class="style21" >
<font style="font-family: 宋体; font-size:12px;">验收结论：合格</font></td>
<td class="style21">
<font style="font-family: 宋体; font-size:12px;">验收人：<input id="Text3" maxlength="6" style="border-width:0px; border-color:Transparent ; width:50px; font-family:宋体 ; font-size:12px;" value='<%=ds.Tables[0].Rows[0]["AuditingUser"]%>' /></font>
</td>
<td class="style21">
<font style="font-family: 宋体; font-size:12px;">送货人：<input id="senduser" maxlength="6" style="border-width:0px; border-color:Transparent ; width:50px; font-family:宋体 ; font-size:12px;" value="" /></font>
</td>
<td class="style21">
<font style="font-family: 宋体; font-size:12px;">保管员：<input id="stockuser" maxlength="6" style="border-width:0px; border-color:Transparent ; width:50px; font-family:宋体 ; font-size:12px;" value='<%=ds.Tables[0].Rows[0]["stockUserName"]%>'/></font>
</td>
<td colspan="2" class="style21">
<font style="font-family: 宋体; font-size:12px;">制单人：<input id="appuser" name="username" maxlength="6" onchange="setTbConSame(this)" style="border-width:0px; border-color:Transparent ; width:50px; font-family:宋体 ; font-size:12px;" value='<%=ds.Tables[0].Rows[0]["AppUserName"] %>' /></font>
</td>
</tr>
<tr>
<td height="6mm">
<font style="font-family: 宋体; font-size:12px;">白 联：存根联</font></td>
<td>
<font style="font-family: 宋体; font-size:12px;">红 联：财务联</font></td>
<td>
<font style="font-family: 宋体; font-size:12px;">黄 联：库房</font></td>
<td>
</td>
<td>
<font style="font-family: 宋体; font-size:12px;">P.<%=((int)(i-1)/5)+1 %>/<%=mypage%></font></td>
</tr>
</table>
</td>
</tr>
</table>
<%
}
%>
</td>
</tr>
</table>
</form>
</body>
</html>
后端代码：
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString["purchaseid"] != null)
{
}
else
{
Response.Redirect("ProPurchaseShow.aspx");
return;
}
}
/// <summary>
/// 获取采购表单基本信息
/// </summary>
/// <returns></returns>
public DataSet GetDataSet()
{
string id = Request.QueryString["purchaseid"].ToString();
DataSet ds= SqlComm.GetDataByCondition("dbo.View_PurchaseInfoPrint", "*", "PurchaseID=" + id);
return ds;
}
/// <summary>
/// 采购单相应批号信息
/// </summary>
/// <returns>DataSet</returns>
public DataSet GetProBatchsDataSet()
{
string id = Request.QueryString["purchaseid"].ToString();
DataSet ds = SqlComm.GetDataByCondition("dbo.View_PurchaseBatchInfoPrint", "*", "PurchaseID=" + id);
return ds;
}
