mes-client/UAS-出货标签管理(贸易版)/PublicMethod/LogicHandler.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using UAS_LabelMachine.Entity;

namespace UAS_LabelMachine.PublicMethod
{
    class LogicHandler
    {
        public static DataHelper dh = SystemInf.dh;

        /// <summary>
        /// 特殊业务过滤逻辑，目前只有海创在使用
        /// </summary>
        /// <param name="iCustCode"></param>
        /// <param name="iBrand"></param>
        /// <param name="iSpec"></param>
        /// <param name="iQty"></param>
        /// <param name="iDateCode"></param>
        /// <param name="iLotno"></param>
        /// <param name="oSpec"></param>
        /// <param name="oQty"></param>
        /// <param name="oDatecode"></param>
        /// <param name="oLotno"></param>
        public static void FilterData(string iCustCode, string iBrand, string iSpec, string iQty, string iDateCode, string iLotno, out string oSpec, out string oQty, out string oDatecode, out string oLotno)
        {
            oSpec = "";
            oQty = "";
            oDatecode = "";
            oLotno = "";
            string[] param = new string[] { iCustCode, iBrand, iSpec, iQty, iDateCode, iLotno, oSpec, oQty, oDatecode, oLotno };
            dh.CallProcedure("sp_datafilter", ref param);
            oSpec = param[6];
            oQty = param[7];
            oDatecode = param[8];
            oLotno = param[9];
        }

        /// <summary>
        /// 通用方法，日期格式转换，转换成指定的年月日
        /// </summary>
        /// <param name="iDatecode"></param>
        /// <param name="iCustcode"></param>
        /// <param name="oYear"></param>
        /// <param name="oMonth"></param>
        /// <param name="oDay"></param>
        /// <param name="oDate"></param>
        public static void GetTimeFromDatecode(string iDatecode, string iCustcode, out string oYear, out string oMonth, out string oDay, out string oDate)
        {
            oYear = "0";
            oMonth = "0";
            oDay = "0";
            oDate = "19000101";
            string[] param = new string[] { iDatecode, iCustcode, oYear, oMonth, oDay, oDate };
            dh.CallProcedure("GETTIMEFROMDATECODE", ref param);
            oYear = param[2];
            oMonth = param[3];
            oDay = param[4];
            oDate = param[5];
        }

        /// <summary>
        /// 特殊业务条码处理，目前针对海创长城客户
        /// </summary>
        /// <param name="iInoutno"></param>
        /// <param name="oSQL"></param>
        public static void CustBarCode(string iInoutno, out string oSQL)
        {
            oSQL = "";
            string[] param = new string[] { iInoutno, oSQL };
            dh.CallProcedure("SP_CUSTBARCODE", ref param);
            oSQL = param[1];
        }

        /// <summary>
        /// 获取生成条码的SQL，目前添加用户海创和凯而高，凯而高有特殊定制逻辑
        /// </summary>
        /// <param name="iPiid"></param>
        /// <param name="iCustCode"></param>
        /// <param name="oSQL"></param>
        public static void GenerateBarCode(string iPiid, string iCustCode, out string oSQL)
        {
            oSQL = "";
            string[] param = new string[] { iPiid, iCustCode, oSQL };
            dh.CallProcedure("sp_GenerateBarCode", ref param);
            oSQL = param[2];
        }

        public static void ImportExcel(string iMaster, string iFileName, string iPiID, string iInOutNo, string iCustCode)
        {
            switch (iMaster)
            {
                //海创
                case "N_HC":
                    ImportExcel_HC(iFileName, iPiID, iInOutNo, iCustCode);
                    break;
                case "SZSI_TEST":
                    ImportExcel_SZSI(iFileName, iPiID, iInOutNo, iCustCode);
                    break;
                case "SZSI":
                    ImportExcel_SZSI(iFileName, iPiID, iInOutNo, iCustCode);
                    break;
                default:
                    break;
            }
        }

        public static void ImportExcel_SZSI(string iFileName, string iPiID, string iInOutNo, string iCustCode)
        {
            StringBuilder sql = new StringBuilder();
            //获取最大的流水号
            DataTable dt = ExcelHandler.ExcelToDataTable(iFileName, true);
            if (dt == null)
            {
                MessageBox.Show("文件" + iFileName + "被占用或者格式不正确");
                return;
            }
            ArrayList<string> detno = new ArrayList<string>();
            ArrayList<string> batch = new ArrayList<string>();
            ArrayList<string> gk = new ArrayList<string>();
            ArrayList<string> weigh = new ArrayList<string>();
            ArrayList<string> qty = new ArrayList<string>();
            ArrayList<string> outboxcode1 = new ArrayList<string>();
            string prcode = dh.getFieldDataByCondition("prodiodetail", "pd_prodcode", "pd_piid=" + iPiID).ToString();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Columns.Contains("序号")) { detno.Add(dt.Rows[i]["序号"]); } else detno.Add("");
                if (dt.Columns.Contains("批号")) { batch.Add(dt.Rows[i]["批号"]); } else batch.Add("");
                if (dt.Columns.Contains("g/K")) { gk.Add(dt.Rows[i]["g/K"]); } else gk.Add("");
                if (dt.Columns.Contains("净重（g）")) { weigh.Add(dt.Rows[i]["净重（g）"]); } else weigh.Add("");
                if (dt.Columns.Contains("数量")) { qty.Add(dt.Rows[i]["数量"]); } else qty.Add("");
                if (dt.Columns.Contains("盒号")) { outboxcode1.Add(dt.Rows[i]["盒号"]); } else outboxcode1.Add("");
            }
            sql.Clear();
            sql.Append("insert into prodiobarcode (pib_id,pib_piid,pib_inoutno,pib_pdno,pib_custbarcode,pib_gk,pib_weigh,pib_qty,pib_outboxcode1,pib_prodcode) values(prodiobarcode_seq.nextval,'" + iPiID + "',");
            sql.Append("'" + iInOutNo + "',:pib_pdno,:pib_lotno,:pib_gk,:pib_weiht,:pib_qty,:pib_outboxcode1,'" + prcode + "')");
            dh.BatchInsert(sql.ToString(), new string[] { "pib_pdno", "pib_lotno", "pib_gk", "pib_weiht", "pib_qty", "pib_outboxcode1" }, detno.ToArray(), batch.ToArray(), gk.ToArray(), weigh.ToArray(), qty.ToArray(), outboxcode1.ToArray());

        }

        //海创的导入方法
        public static void ImportExcel_HC(string iFileName, string iPiID, string iInOutNo, string iCustCode)
        {
            StringBuilder sql = new StringBuilder();
            //获取最大的流水号
            DataTable dt = ExcelHandler.ExcelToDataTable(iFileName, true);
            if (dt == null)
            {
                MessageBox.Show("文件" + iFileName + "被占用或者格式不正确");
                return;
            }
            string outsql = "";
            GenerateBarCode(iPiID, iCustCode, out outsql);
            sql.Clear();
            sql.Append(outsql);
            DataTable dt1 = (DataTable)dh.ExecuteSql(sql.ToString(), "select");
            //需要插入的参数
            ArrayList<string> barcode = new ArrayList<string>();
            ArrayList<string> custbarcode = new ArrayList<string>();
            ArrayList<string> datecode = new ArrayList<string>();
            ArrayList<string> lotno = new ArrayList<string>();
            ArrayList<string> pib_inqty = new ArrayList<string>();
            ArrayList<string> piboutboxcode2 = new ArrayList<string>();
            ArrayList<string> pdordercode = new ArrayList<string>();
            ArrayList<string> pdorderdetno = new ArrayList<string>();
            ArrayList<string> pdpdno = new ArrayList<string>();
            ArrayList<string> pdid = new ArrayList<string>();
            ArrayList<string> prbrand = new ArrayList<string>();
            ArrayList<string> pdprodcode = new ArrayList<string>();
            ArrayList<string> pib_custoutboxcode = new ArrayList<string>();
            ArrayList<string> brand = new ArrayList<string>();
            ArrayList<string> madein = new ArrayList<string>();
            ArrayList<string> prdetail = new ArrayList<string>();
            ArrayList<string> ordercode = new ArrayList<string>();
            ArrayList<string> size = new ArrayList<string>();
            ArrayList<string> weight = new ArrayList<string>();
            //用于数量的校验
            Dictionary<string, decimal> sumqty = new Dictionary<string, decimal>();
            Dictionary<string, Dictionary<string, decimal>> outsumqty = new Dictionary<string, Dictionary<string, decimal>>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string pr_spec = dt.Rows[i]["规格型号"].ToString().Trim().ToUpper();
                if (pr_spec.Trim() == "")
                {
                    break;
                }
                string qty = Regex.Replace(dt.Rows[i]["数量"].ToString(), @"[^\d]*", ""); ;
                //累计Excel该型号的数量
                if (!sumqty.ContainsKey(pr_spec))
                {
                    sumqty.Add(pr_spec, 0);
                }
                //累计Excel该型号的数量
                sumqty[pr_spec] += decimal.Parse(qty);
            }
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                //不包含型号则进行添加
                string pr_spec = dt1.Rows[i]["pr_spec"].ToString().Trim();
                string pd_pdno = dt1.Rows[i]["pd_pdno"].ToString();
                decimal pd_outqty = decimal.Parse(dt1.Rows[i]["pd_outqty"].ToString());
                if (!outsumqty.ContainsKey(pr_spec))
                {
                    Dictionary<string, decimal> dic = new Dictionary<string, decimal>();
                    dic.Add(pd_pdno, pd_outqty);
                    outsumqty.Add(pr_spec, dic);
                }
                else
                {
                    outsumqty[pr_spec].Add(pd_pdno, pd_outqty);
                }
            }
            //上一个的最小外箱号，如果箱号没有发生变化则外箱条码也不变化
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string pr_spec = dt.Rows[i]["规格型号"].ToString().Trim().ToUpper();
                string 品牌 = dt.Rows[i]["品牌"].ToString().Trim();
                string 产地 = dt.Rows[i]["产地"].ToString().Trim();
                string 尺寸 = dt.Rows[i]["尺寸"].ToString().Trim();
                string 重量 = dt.Rows[i]["重量"].ToString().Trim();
                string 产品名称 = dt.Rows[i]["产品名称"].ToString().Trim();
                string 订单编号 = dt.Rows[i]["订单编号"].ToString().Trim();
                if (pr_spec.Trim() == "")
                {
                    break;
                }
                string qty = dt.Rows[i]["数量"].ToString();
                string DateCode = "";
                string LotNo = "";
                if (dt.Columns.Contains("DC/LOT NO"))
                {
                    if (dt.Rows[i]["DC/LOT NO"].ToString().IndexOf("-") > 0)
                    {
                        DateCode = dt.Rows[i]["DC/LOT NO"].ToString().Split('-')[0];
                        LotNo = dt.Rows[i]["DC/LOT NO"].ToString().Split('-')[1];
                    }
                    else if (dt.Rows[i]["DC/LOT NO"].ToString().IndexOf(" ") > 0)
                    {
                        DateCode = dt.Rows[i]["DC/LOT NO"].ToString().Split(' ')[0];
                        LotNo = dt.Rows[i]["DC/LOT NO"].ToString().Split(' ')[1];
                    }
                }
                if (dt.Columns.Contains("DC") && DateCode == "")
                {
                    DateCode = dt.Rows[i]["DC"].ToString();
                }
                if (dt.Columns.Contains("LOT NO") && LotNo == "")
                {
                    LotNo = dt.Rows[i]["LOT NO"].ToString();
                }
                string pib_outboxcode2 = dt.Rows[i]["箱号"].ToString();
                DataRow[] dr = dt1.Select("pr_spec='" + pr_spec + "'");
                if (dr.Length == 0)
                {
                    FilterData(iCustCode, "FUDAN", pr_spec, qty, DateCode, LotNo, out pr_spec, out qty, out DateCode, out LotNo);
                    dr = dt1.Select("pr_spec='" + pr_spec + "'");
                }
                else
                {
                    FilterData(iCustCode, dr[0]["pr_brand"].ToString(), pr_spec, qty, DateCode, LotNo, out pr_spec, out qty, out DateCode, out LotNo);
                }
                if (dr.Length == 0)
                {
                    MessageBox.Show("出货单" + iInOutNo + "不包含型号" + pr_spec);
                    return;
                }
                decimal outqty = decimal.Parse(Regex.Replace(qty, @"[^\d]*", ""));
                for (int k = 0; k < dr.Length; k++)
                {
                    string pd_orderdetno = dr[k]["pd_orderdetno"].ToString();
                    string pd_prodcode = dr[k]["pd_prodcode"].ToString();
                    string pd_ordercode = dr[k]["pd_ordercode"].ToString();
                    string pd_piid = dr[k]["pd_piid"].ToString();
                    string pr_brand = dr[k]["pr_brand"].ToString();
                    string pd_pdno = dr[k]["pd_pdno"].ToString();
                    string pd_id = dr[k]["pd_id"].ToString();
                    string pd_piclass = dr[k]["pd_piclass"].ToString();
                    string pr_id = dr[k]["pr_id"].ToString();
                    string pr_madein = dr[k]["pr_madein"].ToString();
                    //已导入数量
                    decimal zxbzs = decimal.Parse(dr[k]["pr_zxbzs"].ToString());
                    decimal pd_outqty = 0;
                    decimal barcodenum = 0;
                    if (outsumqty[pr_spec][pd_pdno] == 0)
                    {
                        continue;
                    }
                    //如果导入行的数量小于改明细的出货数量
                    if (outsumqty[pr_spec][pd_pdno] >= outqty)
                    {
                        //使用导入明细行数量
                        pd_outqty = outqty;
                    }
                    else
                    {
                        ////使用该出货单明细行数量,并且数量置为0
                        pd_outqty = outsumqty[pr_spec][pd_pdno];
                    }
                    string pib_barcode = dh.getFieldDataByCondition("prodiobarcode", "max(pib_barcode)", "PIB_INOUTNO='" + iInOutNo + "'").ToString();
                    if (pd_outqty % zxbzs != 0)
                    {
                        barcodenum = Math.Floor(pd_outqty / zxbzs) + 1;
                        //如果有余数先加上所有最小包最后加尾数
                        for (int j = 0; j < barcodenum - 1; j++)
                        {
                            barcode.Add(BaseUtil.BarcodeMethod1(pd_id, pr_id, pib_barcode));
                            custbarcode.Add(BarcodeMethod1(UAS_出货标签打印.PrefixFixed, UAS_出货标签打印.Suffix, UAS_出货标签打印.SerialNumIndex, UAS_出货标签打印.SerialNumLength, UAS_出货标签打印.Radix));
                            pib_custoutboxcode.Add(pib_outboxcode2);
                            piboutboxcode2.Add(pib_outboxcode2);
                            pib_inqty.Add(zxbzs);
                            datecode.Add(DateCode);
                            lotno.Add(LotNo);
                            pdordercode.Add(订单编号 == "" ? pd_ordercode : 订单编号);
                            pdorderdetno.Add(pd_orderdetno);
                            pdpdno.Add(pd_pdno);
                            pdid.Add(pd_id);
                            prbrand.Add(品牌 == "" ? pr_brand : 品牌);
                            madein.Add(产地 == "" ? pr_madein : 产地);
                            size.Add(尺寸);
                            weight.Add(重量);
                            prdetail.Add(产品名称);
                            ordercode.Add(订单编号);
                            pdprodcode.Add(pd_prodcode);
                        }
                        custbarcode.Add(BarcodeMethod1(UAS_出货标签打印.PrefixFixed, UAS_出货标签打印.Suffix, UAS_出货标签打印.SerialNumIndex, UAS_出货标签打印.SerialNumLength, UAS_出货标签打印.Radix));
                        pib_custoutboxcode.Add(pib_outboxcode2);
                        barcode.Add(BaseUtil.BarcodeMethod1(pd_id, pr_id, pib_barcode));
                        pib_inqty.Add(pd_outqty % zxbzs);
                        piboutboxcode2.Add(pib_outboxcode2);
                        datecode.Add(DateCode);
                        lotno.Add(LotNo);
                        pdordercode.Add(订单编号 == "" ? pd_ordercode : 订单编号);
                        pdorderdetno.Add(pd_orderdetno);
                        pdpdno.Add(pd_pdno);
                        pdid.Add(pd_id);
                        prbrand.Add(品牌 == "" ? pr_brand : 品牌);
                        madein.Add(产地 == "" ? pr_madein : 产地);
                        size.Add(尺寸);
                        weight.Add(重量);
                        prdetail.Add(产品名称);
                        ordercode.Add(订单编号);
                        pdprodcode.Add(pd_prodcode);
                    }
                    else
                    {
                        barcodenum = pd_outqty / zxbzs;
                        for (int j = 0; j < barcodenum; j++)
                        {
                            barcode.Add(BaseUtil.BarcodeMethod1(pd_id, pr_id, pib_barcode));
                            custbarcode.Add(BarcodeMethod1(UAS_出货标签打印.PrefixFixed, UAS_出货标签打印.Suffix, UAS_出货标签打印.SerialNumIndex, UAS_出货标签打印.SerialNumLength, UAS_出货标签打印.Radix));
                            pib_custoutboxcode.Add(pib_outboxcode2);
                            pib_inqty.Add(zxbzs);
                            piboutboxcode2.Add(pib_outboxcode2);
                            datecode.Add(DateCode);
                            lotno.Add(LotNo);
                            pdordercode.Add(订单编号 == "" ? pd_ordercode : 订单编号);
                            pdorderdetno.Add(pd_orderdetno);
                            pdpdno.Add(pd_pdno);
                            pdid.Add(pd_id);
                            prbrand.Add(品牌 == "" ? pr_brand : 品牌);
                            madein.Add(产地 == "" ? pr_madein : 产地);
                            size.Add(尺寸);
                            weight.Add(重量);
                            prdetail.Add(产品名称);
                            pdprodcode.Add(pd_prodcode);
                        }
                    }
                    outqty -= outsumqty[pr_spec][pd_pdno];
                    outsumqty[pr_spec][pd_pdno] -= pd_outqty;
                }
            }
            string Lastoutbox = "";
            string Lastoutboxcode = "";
            List<string> custoutboxcode = new List<string>();
            //根据记录的箱号，在料盘的流水之后拼接外箱的流水
            for (int i = 0; i < pib_custoutboxcode.Count; i++)
            {
                if (Lastoutbox != pib_custoutboxcode[i].ToString())
                {
                    Lastoutboxcode = BarcodeMethod1(UAS_出货标签打印.PrefixFixed, UAS_出货标签打印.Suffix, UAS_出货标签打印.SerialNumIndex, UAS_出货标签打印.SerialNumLength, UAS_出货标签打印.Radix);
                    custoutboxcode.Add(Lastoutboxcode);
                    Lastoutbox = pib_custoutboxcode[i].ToString();
                }
                else
                {
                    custoutboxcode.Add(Lastoutboxcode);
                }
            }
            //插叙所有的统计数量
            string ErrMsg = "";
            foreach (var item in sumqty)
            {
                dt = (DataTable)dh.ExecuteSql("select nvl(sum(pd_outqty),0)pd_outqty,pr_spec from prodiodetail left join product on pr_code=pd_prodcode where pd_piid='" + iPiID + "' group by pr_spec", "select");
                dt1 = (DataTable)dh.ExecuteSql("select nvl(sum(pib_qty),0)pib_qty,pr_spec  from prodiobarcode left join product on pib_prodcode=pr_code where pib_piid='" + iPiID + "' group by pr_spec", "select");
                DataRow[] dr = dt.Select("pr_spec='" + item.Key + "'");
                DataRow[] dr1 = dt1.Select("pr_spec='" + item.Key + "'");
                if (dr.Length > 0)
                {
                    decimal pd_outqty = decimal.Parse(dr[0]["pd_outqty"].ToString());
                    decimal pib_qty = 0;
                    //如果有已导入的数量
                    if (dr1.Length > 0)
                    {
                        pib_qty = decimal.Parse(dr1[0]["pib_qty"].ToString());
                    }
                    //本次导入的数量和原有导入数量累加和出库数量做对比
                    if (pd_outqty < item.Value + pib_qty)
                    {
                        ErrMsg += "型号" + dr[0]["pr_spec"].ToString() + "出货数量" + pd_outqty + ",已导入数量" + pib_qty + ",本次导入" + item.Value + ",超出数量" + (item.Value + pib_qty - pd_outqty) + "\n";
                    }
                }
            }
            if (ErrMsg == "")
            {
                if (pdprodcode.Count > 0)
                {
                    sql.Clear();
                    sql.Append("insert into prodiobarcode (PIB_ID,PIB_PRODCODE,pib_inman,PIB_INDATE,PIB_INOUTNO,PIB_PIID,pib_brand,PIB_BARCODE,PIB_PDNO,");
                    sql.Append("PIB_PDID,PIB_PICLASS,PIB_QTY,pib_datecode,pib_lotno,PIB_IFPRINT,PIB_IFPICK,PIB_ORDERCODE,pib_orderdetno,");
                    sql.Append("pib_outboxcode2,pib_custbarcode,pib_custoutboxcode,pib_madein,pib_size,pib_weight,pib_prdetail)values(prodiobarcode_seq.nextval,:pd_prodcode,'" + User.UserName + "',sysdate,");
                    sql.Append("'" + iInOutNo + "'," + iPiID + ",:pr_brand,:barcode,:pd_pdno,:pd_id,'',:pib_inqty,");
                    sql.Append(":pib_datecode,:pib_lotno,0,-1,:pd_ordercode,:pd_orderdetno,:outboxcode,:custbarcode,:pib_custoutboxcode,:pib_madein,:pib_size,:pib_weight,:pib_prdetail)");
                    dh.BatchInsert(sql.ToString(), new string[] { "pd_prodcode", "pr_brand", "barcode", "pd_pdno", "pd_id", "pib_inqty", "pib_datecode", "pib_lotno", "pd_ordercode", "pd_orderdetno", "outboxcode", "custbarcode", "pib_custoutboxcode", "pib_madein", "pib_size", "pib_weight", "pib_prdetail" }, pdprodcode.ToArray(), prbrand.ToArray(), barcode.ToArray(), pdpdno.ToArray(), pdid.ToArray(), pib_inqty.ToArray(), datecode.ToArray(), lotno.ToArray(), pdordercode.ToArray(), pdorderdetno.ToArray(), piboutboxcode2.ToArray(), custbarcode.ToArray(), custoutboxcode.ToArray(), madein.ToArray(), size.ToArray(), weight.ToArray(), prdetail.ToArray());
                    //更新流水
                    dh.UpdateByCondition("RuleMaxNum", "rmn_maxnumber='" + UAS_出货标签打印.custserialnum + "'", "rmn_nrcode='" + UAS_出货标签打印.NrCode + "' and rmn_prefix='" + UAS_出货标签打印.Prefix + "'");
                }
                else
                {
                    MessageBox.Show("解析数据不包含出货单数据，请重新导入", "提示");
                }
            }
            else
            {
                MessageBox.Show(ErrMsg, "超出数量提醒");
            }
        }

        //生成客户条码
        public static string BarcodeMethod1(string Prefix, string Suffix, int Index, int Length, int radix)
        {
            string str = Prefix;
            //如果是流水则需要在前面加0
            string serialcode = BaseUtil.DToAny(UAS_出货标签打印.custserialnum, radix);
            for (int j = serialcode.ToString().Length; j < Length; j++)
            {
                serialcode = "0" + serialcode;
            }
            str += serialcode;
            str += Suffix;
            UAS_出货标签打印.custserialnum = UAS_出货标签打印.custserialnum + 1;
            return str;
        }
    }
}