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;
        }