Exemplo n.º 1
0
        //写入单身信息
        private void SetDetailInfo(ERP_Create_Purtg_HeadObject headObj, ERP_Create_Purtg_DetailObject detailObj)
        {
            detailObj.TH015 = detailObj.TH007;
            detailObj.TH016 = detailObj.TH007;
            detailObj.TH034 = detailObj.TH007;
            detailObj.TH064 = detailObj.TH008;
            detailObj.TH065 = detailObj.TH008;
            detailObj.TH019 = Math.Round(float.Parse(detailObj.TH007) * float.Parse(detailObj.TH018), 6, MidpointRounding.AwayFromZero).ToString();
            detailObj.TH003 = detailObj.RowIndex.ToString().PadLeft(4, '0');

            string sqlstr = @"INSERT INTO PURTH(COMPANY,CREATOR,USR_GROUP,CREATE_DATE,FLAG, 
                                TH001,TH002,TH003,TH004,TH005,TH006,TH007,TH008,TH009,TH010, 
                                TH011,TH012,TH013,TH014,TH015,TH016,TH018,TH019,TH026,TH027, 
                                TH029,TH030,TH031,TH032,TH033,TH034,TH035,TH042,TH043,TH044, 
                                TH060,TH064,TH065,TH071,TH072,THC02) 
                                VALUES('{0}','{1}','{2}','{3}',1,'{4}','{5}','{6}', 
                                '{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}', 
                                '{18}','{19}','{20}','{21}','N','{22}','N','N','N','N','{23}', 
                                '{24}','{25}','{26}','N','N','0','{27}','{28}','1','##########','{29}')";

            mssql.SQLexcute(strConnection, string.Format(sqlstr, headObj.Company, headObj.Uid, headObj.Ugroup, headObj.Time, headObj.TG001, headObj.TG002,
                                                         detailObj.TH003, detailObj.TH004, detailObj.TH005, detailObj.TH006, detailObj.TH007, detailObj.TH008, detailObj.TH009, detailObj.TH010,
                                                         detailObj.TH011, detailObj.TH012, detailObj.TH013, detailObj.TH014, detailObj.TH015, detailObj.TH016, detailObj.TH018, detailObj.TH019,
                                                         detailObj.TH027, detailObj.TH033, detailObj.TH034, detailObj.TH035, detailObj.TH042, detailObj.TH064, detailObj.TH065, detailObj.THC02));
        }
Exemplo n.º 2
0
        private void SetDetailDefaultDef(ERP_Create_Purtg_HeadObject headObj)
        {
            ERP_Create_Purtg_DetailObject detailObj = new ERP_Create_Purtg_DetailObject();

            GetLsDt(headObj, detailObj);
            if (detailObj.LsDt != null)
            {
                for (int lsRowIndex = 0; lsRowIndex < detailObj.LsDt.Rows.Count; lsRowIndex++)
                {
                    try
                    {
                        detailObj.TH004 = detailObj.LsDt.Rows[lsRowIndex][0].ToString();
                        detailObj.TH005 = detailObj.LsDt.Rows[lsRowIndex][1].ToString();
                        detailObj.TH006 = detailObj.LsDt.Rows[lsRowIndex][2].ToString();
                        detailObj.TH008 = detailObj.LsDt.Rows[lsRowIndex][3].ToString();
                        detailObj.TH009 = detailObj.LsDt.Rows[lsRowIndex][4].ToString();
                        detailObj.Total = float.Parse(detailObj.LsDt.Rows[lsRowIndex][5].ToString());

                        detailObj.TH010 = headObj.TG005;
                        detailObj.TH014 = headObj.TG003;
                        GetSlDt(headObj, detailObj);
                        GetNumber(headObj, detailObj);
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.ToString());
                    }
                }
            }
        }
Exemplo n.º 3
0
        //数量
        private void GetNumber(ERP_Create_Purtg_HeadObject headObj, ERP_Create_Purtg_DetailObject detailObj)
        {
            if (detailObj.SlDt != null && detailObj.SlDt.Rows.Count != 0)
            {
                detailObj.RowIndex++;

                detailObj.TH011 = detailObj.SlDt.Rows[0][1].ToString();
                detailObj.TH012 = detailObj.SlDt.Rows[0][2].ToString();
                detailObj.TH013 = detailObj.SlDt.Rows[0][3].ToString();
                detailObj.TH018 = Math.Round(float.Parse(detailObj.SlDt.Rows[0][4].ToString()), 6, MidpointRounding.AwayFromZero).ToString();
                detailObj.TH033 = detailObj.SlDt.Rows[0][5].ToString();
                detailObj.TH035 = detailObj.SlDt.Rows[0][6].ToString();
                detailObj.TH042 = detailObj.SlDt.Rows[0][7].ToString();
                detailObj.THC02 = detailObj.SlDt.Rows[0][8].ToString();

                if (float.Parse(detailObj.SlDt.Rows[0][0].ToString()) >= detailObj.Total)
                {
                    detailObj.TH007 = Math.Round(detailObj.Total, 6, MidpointRounding.AwayFromZero).ToString();
                    SetDetailInfo(headObj, detailObj);
                }
                else
                {
                    float sl = float.Parse(detailObj.SlDt.Rows[0][0].ToString());
                    detailObj.TH007  = Math.Round(sl, 6, MidpointRounding.AwayFromZero).ToString();
                    detailObj.Total -= sl;
                    SetDetailInfo(headObj, detailObj);
                    detailObj.SlDt.Rows.RemoveAt(0);
                    GetNumber(headObj, detailObj);
                }
            }
        }
Exemplo n.º 4
0
        private void UptJHXAInfo(ERP_Create_Purtg_HeadObject headObj)
        {
            string time   = mssql.SQLselect(strConnection, "select dbo.f_getTime(1)").Rows[0][0].ToString();
            string sqlstr = @"UPDATE COMFORT.dbo.JH_LYXA SET MODIFIER='{1}', MODI_DATE='{2}', 
                                FLAG=(convert(int,COMFORT.dbo.JH_LYXA.FLAG))%999+1, JHXA011 = 'Y', UDF01 = '{3}'WHERE  JHXA005 = '{0}' ";

            mssql.SQLexcute(strConnection, string.Format(sqlstr, headObj.FlowId, headObj.Uid, time, headObj.TG001 + '-' + headObj.TG002));
        }
Exemplo n.º 5
0
        //根据流水号,获获取临时表的明细
        private void GetLsDt(ERP_Create_Purtg_HeadObject headObj, ERP_Create_Purtg_DetailObject detailObj)
        {
            string sqlstr = @"SELECT RTRIM(MB001), RTRIM(MB002), RTRIM(MB003), RTRIM(MB004), 
                                RTRIM(JHXA003), RTRIM(JHXA009) FROM INVMB 
                                INNER JOIN JH_LYXA ON JHXA007 = MB001 WHERE 1=1 AND JHXA005 = '{0}' ORDER BY ID";

            detailObj.LsDt = mssql.SQLselect(strConnection, string.Format(sqlstr, headObj.FlowId));
        }
Exemplo n.º 6
0
        private void GetHeadTime(ERP_Create_Purtg_HeadObject headObj)
        {
            string    sqlstr = "select dbo.f_getTime(1)";
            DataTable dt     = mssql.SQLselect(strConnection, sqlstr);

            if (dt != null)
            {
                headObj.Time = dt.Rows[0][0].ToString();
            }
        }
Exemplo n.º 7
0
        // 写入单头
        private void SetHeadInfo(ERP_Create_Purtg_HeadObject headObj)
        {
            string sqlstr = @"INSERT INTO PURTG (COMPANY, CREATOR, USR_GROUP, CREATE_DATE, FLAG, TG001, TG002, TG003, TG004, TG005, 
                                TG006, TG007, TG008, TG009, TG010, TG013, TG014, TG015, TG021, TG030, TG033, TG016, TG043, TG052) 
                                VALUES('{0}', '{1}', '{2}', '{3}', '1', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', 
                                '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', '', '', '' )";

            mssql.SQLexcute(strConnection, string.Format(sqlstr, headObj.Company, headObj.Uid, headObj.Ugroup, headObj.Time, headObj.TG001,
                                                         headObj.TG002, headObj.TG003, headObj.TG004, headObj.TG005, headObj.TG006, headObj.TG007, headObj.TG008, headObj.TG009,
                                                         headObj.TG010, headObj.TG013, headObj.TG014, headObj.TG015, headObj.TG021, headObj.TG030, headObj.TG033));
        }
Exemplo n.º 8
0
        //更新单身金额信息
        private void UptDetailMoney(ERP_Create_Purtg_HeadObject headObj)
        {
            string sqlstr = @"UPDATE COMFORT.dbo.PURTH  SET 
                                TH045 = CAST(ROUND(TH019/(1+CONVERT(FLOAT, TG030)),2) AS  NUMERIC(10,2)), 
                                TH046 = CAST(ROUND(TH019 - (TH019/(1+CONVERT(FLOAT, TG030))),2) AS  NUMERIC(10,2)), 
                                TH047 = CAST(ROUND((TH019 * CONVERT(FLOAT, TG008)/(1+CONVERT(FLOAT, TG030))),2) 
                                AS  NUMERIC(10,2)), 
                                TH048 = CAST(ROUND((TH019 * CONVERT(FLOAT, TG008)) - 
                                (TH019 * CONVERT(FLOAT, TG008)/(1+CONVERT(FLOAT, TG030))),2) AS  NUMERIC(10,2)) 
                                FROM PURTH INNER JOIN COMFORT.dbo.PURTG AS PURTG ON TG001 = TH001 AND TG002 = TH002 
                                WHERE TG001= '{0}' AND TG002= '{1}' ";

            mssql.SQLexcute(strConnection, string.Format(sqlstr, headObj.TG001, headObj.TG002));
        }
Exemplo n.º 9
0
        private void GetHeadTG002(ERP_Create_Purtg_HeadObject headObj)
        {
            //string sqlstr = @"SELECT (CASE WHEN A1 IS NULL THEN A2 + '0001' ELSE A1 END ) B FROM
            //                    (SELECT MAX(TG002) + 1 A1, SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 112), 3, 4) A2
            //                    FROM PURTG
            //                    WHERE TG001 = '{0}' AND SUBSTRING(TG002, 1, 4) =
            //                    SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 112), 3, 4)) A";
            string    sqlstr = "EXEC dbo.P_GETDH '{0}'";
            DataTable dt     = mssql.SQLselect(strConnection, string.Format(sqlstr, headObj.TG001));

            if (dt != null)
            {
                headObj.TG002 = dt.Rows[0][0].ToString();
            }
        }
Exemplo n.º 10
0
        //更新单头金额信息
        private void UptHeadMoney(ERP_Create_Purtg_HeadObject headObj)
        {
            string sqlstr = @"UPDATE A SET TG017=SUMTH019,TG019=SUMTH046,TG026=SUMTH015,TG028=SUMTH045,TG031=SUMTH047, 
                                TG032=SUMTH048,TG040=SUMTH050,TG041=SUMTH052,TG053=SUMTH007,TG054=SUMTH049 
                                FROM COMFORT.dbo.PURTG A 
                                INNER JOIN (SELECT TH001,TH002,SUMTH019=SUM(TH019),SUMTH046=SUM(TH046), 
                                SUMTH007=SUM(CASE WHEN MA024='2' THEN FLOOR(TH007) ELSE TH007 END), 
                                SUMTH015=SUM(CASE WHEN MA024='2' THEN FLOOR(TH015) ELSE TH015 END),SUMTH045=SUM(TH045), 
                                SUMTH047=SUM(TH047),SUMTH048=SUM(TH048),SUMTH050=SUM(TH050),SUMTH052=SUM(TH052), 
                                SUMTH049=SUM(TH049) 
                                FROM COMFORT.dbo.PURTH 
                                INNER JOIN COMFORT.dbo.CMSMA ON 1=1 
                                GROUP BY TH001,TH002)  AS B ON A.TG001=B.TH001 AND A.TG002=B.TH002 
                                WHERE TG001= '{0}' AND TG002= '{1}' ";

            mssql.SQLexcute(strConnection, string.Format(sqlstr, headObj.TG001, headObj.TG002));
        }
Exemplo n.º 11
0
        //更新单头税率,汇率
        private void UptHeadInfo2(ERP_Create_Purtg_HeadObject headObj)
        {
            string sqlstr = @"UPDATE COMFORT.dbo.PURTG SET TG008 = ISNULL(MG003, 1) FROM COMFORT.dbo.PURTG 
                            LEFT JOIN( 
                                SELECT G.MG001, G.MG003 FROM(
                                    SELECT MG001, MAX(MG002) MG002 FROM CMSMG
                                    WHERE 1 = 1
                                    AND MG002 <= LEFT(dbo.f_getTime(1), 8)
                                    GROUP BY MG001
                                ) AS K LEFT JOIN COMFORT.dbo.CMSMG AS G ON K.MG001 = G.MG001 AND K.MG002 = G.MG002
                            ) AS A ON TG007 = A.MG001
                            WHERE TG001 = '{0}' AND TG002 = '{1}'

                            UPDATE COMFORT.dbo.PURTG SET TG030 = ISNULL(MA004, 0) FROM COMFORT.dbo.PURTG LEFT JOIN COMFORT.dbo.CMSMA ON CMSMA.COMPANY = 'COMFORT' 
                            WHERE TG001 = '{0}' AND TG002 ='{1}'  ";

            mssql.SQLexcute(strConnection, string.Format(sqlstr, headObj.TG001, headObj.TG002));
        }
Exemplo n.º 12
0
        public string HandelDef(string flowID)
        {
            ERP_Create_Purtg_HeadObject headObj = new ERP_Create_Purtg_HeadObject();

            headObj.FlowId = flowID;

            GetHeadInfo(headObj);
            GetHeadTG002(headObj);
            GetHeadTime(headObj);
            SetHeadInfo(headObj);
            UptHeadInfo2(headObj);

            SetDetailDef(headObj);
            UptDetailMoney(headObj);
            UptHeadMoney(headObj);
            UptJHXAInfo(headObj);
            return(headObj.TG001 + '-' + headObj.TG002);
        }
Exemplo n.º 13
0
        //可进货采购单明细逻辑
        private void GetSlDt(ERP_Create_Purtg_HeadObject headObj, ERP_Create_Purtg_DetailObject detailObj)
        {
            string sqlstr = @"SELECT DISTINCT TOP 200 TD008 - TD015 - ( SELECT isnull( SUM ( TH007 ), 0 ) 
                                FROM COMFORT.dbo.PURTH(NOLOCK) AS PURTH 
                                INNER JOIN COMFORT.dbo.PURTG(NOLOCK) AS PURTG ON TG001 = TH001 AND TG002 = TH002
                                WHERE TH011 = TD001 AND TH012 = TD002 AND TH013 = TD003 
                                AND TG013 = 'N'
                                ) AS WJL, 
                                TD001 AS TH011, RTRIM(TD002) AS TH012, TD003 AS TH013, (CASE WHEN TD010 IS NULL THEN 0 ELSE TD010 END) AS TH018, TD014 AS TH033, RTRIM(TD020) AS TH035, 
                                RTRIM(TD022) AS TH042, RTRIM(TDC03) AS THC02, TC003, TD012 
                                FROM COMFORT.dbo.PURTD(NOLOCK) AS PURTD 
                                LEFT JOIN COMFORT.dbo.PURTC(NOLOCK) AS PURTC ON TC001 = TD001 AND TC002 = TD002 
                                WHERE TC004 = '{0}' AND TD004 = '{1}' 
                                AND (TD008 - TD015 - ( SELECT isnull( SUM ( TH007 ), 0 ) FROM COMFORT.dbo.PURTH PURTH 
                                WHERE TH011 = TD001 AND TH012 = TD002 AND TH013 = TD003 AND TH030 = 'N' )) > 0 
                                AND TD016 = 'N' AND TC014 = 'Y' AND TC001 <> '3305' AND TC001 <> '3306' 
                                ORDER BY TC003 DESC, TD012, TD001 DESC, RTRIM(TD002), TD003";

            detailObj.SlDt = mssql.SQLselect(strConnection, string.Format(sqlstr, headObj.TG005, detailObj.TH004));
        }
Exemplo n.º 14
0
        private void GetHeadInfo(ERP_Create_Purtg_HeadObject headObj)
        {
            string    sqlstr = @"SELECT DISTINCT RTRIM(JHXA.COMPANY) 公司别, RTRIM(JHXA.CREATOR) 创建人, RTRIM(JHXA.USR_GROUP) 用户组, 
                                RTRIM(JHXA001) 进货单别, RTRIM(JHXA004) 进货日期, RTRIM(JHXA002) 供应商编号, RTRIM(JHXA013) 送货单号, 
                                RTRIM(MA021) 交易币种, MG2.MG003 汇率, 
                                RTRIM(MA030) 发票种类, 
                                (CASE WHEN NOT (TC018 = '' OR TC018 IS NULL) THEN TC018 
                                ELSE (CASE WHEN MA044 ='' OR MA044 IS NULL THEN '1' ELSE MA044 END ) END) AS TC018C, 
                                RTRIM(MA003) 供应商全称, 
                                (CASE WHEN TC026 IS NULL THEN MA064 ELSE TC026 END) AS TC026C, 
                                (CASE WHEN TC027 = '' OR TC027 IS NULL THEN MA055 ELSE TC027 END) AS TC027C 
                                FROM COMFORT.dbo.JH_LYXA AS JHXA 
                                LEFT JOIN COMFORT.dbo.PURTC AS PURTC ON 1=2 
                                LEFT JOIN COMFORT.dbo.INVMB AS INVMB ON MB001=JHXA007 
                                LEFT JOIN COMFORT.dbo.PURMA AS PURMA ON MA001=JHXA002 
                                LEFT JOIN (SELECT CMSMG.MG003, CMSMG.MG001 FROM COMFORT.dbo.CMSMG 
                                INNER JOIN (SELECT MAX(MG002) MAXMG02, MG001 MAXMG01 FROM CMSMG GROUP BY MG001) AS MG 
                                ON MG.MAXMG01 = CMSMG.MG001 AND MG.MAXMG02 = CMSMG.MG002) AS MG2 ON MG2.MG001 = MA021 
                                WHERE JHXA005 IN ('{0}') AND JHXA011 = 'N' ";
            DataTable dt     = mssql.SQLselect(strConnection, string.Format(sqlstr, headObj.FlowId));

            if (dt != null)
            {
                headObj.Company = dt.Rows[0][0].ToString();
                headObj.Uid     = dt.Rows[0][1].ToString();
                headObj.Ugroup  = dt.Rows[0][2].ToString();
                headObj.TG001   = dt.Rows[0][3].ToString();
                headObj.TG003   = dt.Rows[0][4].ToString();
                headObj.TG005   = dt.Rows[0][5].ToString();
                headObj.TG006   = dt.Rows[0][6].ToString();
                headObj.TG007   = dt.Rows[0][7].ToString();
                headObj.TG008   = dt.Rows[0][8].ToString();
                headObj.TG009   = dt.Rows[0][9].ToString();
                headObj.TG010   = dt.Rows[0][10].ToString();
                headObj.TG014   = headObj.TG003;
                headObj.TG021   = dt.Rows[0][11].ToString();
                headObj.TG030   = dt.Rows[0][12].ToString();
                headObj.TG033   = dt.Rows[0][13].ToString();
            }
        }
Exemplo n.º 15
0
 private void SetDetailDef(ERP_Create_Purtg_HeadObject headObj)
 {
     SetDetailDefaultDef(headObj);
 }