Ejemplo n.º 1
0
        private void ExportToExcel(string mdbFile, string excelFileName)
        {
            if (File.Exists(excelFileName))
            {
                FileInfo FinfoReport = new FileInfo(excelFileName);
                FinfoReport.Delete();
            }
            string exportExcel = "";

            try
            {
                if (_Is_CX)
                {
                    exportExcel  = " SELECT *";
                    exportExcel += "  into [Excel 8.0;database=" + excelFileName + "].[VTable]   FROM   [VTable] WHERE (0=0) ";
                }
                else
                {
                    exportExcel  = @" SELECT   SEQUENCE_NUM,ORDER_NO,OUT_GOOD,OUT_GOODTIME,
                                                        RLENS_SIZE,RLENS_TYPE,RSPH,RCYL,RAXIS,RADDS,RQTY,
                                                        LLENS_SIZE,LLENS_TYPE,LSPH,LCYL,LAXIS,LADDS,LQTY,
                                TINT_CODE,TINT_CONTENT,UV,HARD,COATING,BR,MIR,RCP,LCP,RBASE,LBASE,RKNIFE_SIDER,LKNIFE_SIDER,RDECENTER,LDECENTER,
                                RPRISM,LPRISM,INPUT_DATE,OUTPUT_DATE,PRINTED,PRINT_BAG,REMARK,LENS_MATERIAL,BarCode,SPECIAL_CODE,AURO"                        ;
                    exportExcel += "  into [Excel 8.0;database=" + excelFileName + "].[VTable]   FROM [VTable] WHERE (0=0) ";
                }
                string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbFile;
                DbHelperOledb.ExecuteSql(exportExcel, connectionString);
            }
            catch
            {
                throw;
            }
        }
Ejemplo n.º 2
0
        public int Import(string dbCode, int lgIndex, string fileName)
        {
            DbHelperOledb oledb = new DbHelperOledb();
            DataSet       ds    = oledb.ChangeExcelToDataSet(fileName);
            DbHelperSQL   _DbH  = new DbHelperSQL(dbCode);

            using (SqlConnection conn = new SqlConnection(_DbH.ConnectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection     = conn;
                    cmd.Transaction    = trans;
                    cmd.CommandTimeout = 200;
                    try
                    {
                        this.PrepareImport(cmd, ds);
                        trans.Commit();
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
            return(ds.Tables[0].Rows.Count);
        }
Ejemplo n.º 3
0
        public int Import(string dbCode, int lgIndex, string fileName, string BID)
        {
            DbHelperOledb oledb = new DbHelperOledb();
            DataSet       ds    = oledb.ChangeExcelToDataSet(fileName);

            Hashtable htstrSqlMain = new Hashtable();
            Hashtable htstrSqlSub  = new Hashtable();

            SqlParameter[] parameters = null;
            StringBuilder  strSql     = new StringBuilder();

            strSql.Append("Delete Sale_PriceContract_Frame where BID=@BID ;");
            parameters = new SqlParameter[] {
                new SqlParameter("@BID", SqlDbType.VarChar, 25)
            };
            parameters[0].Value = BID;
            htstrSqlMain.Add(strSql.ToString(), parameters);
            strSql.Clear();

            int i = 1;

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                strSql.Append("delete Sale_PriceContract_Frame ");
                strSql.Append("where BID=@BID and FrameCode=@FrameCode ;");
                ////////////////////////
                strSql.Append("insert into Sale_PriceContract_Frame(");
                strSql.Append("ID,BID,FrameCode,Price,PriceJM,InvTitle)");
                strSql.Append(" values (");
                strSql.Append("(select HKOERP.dbo.SF_GetID()),@BID,@FrameCode,@Price,@PriceJM,@InvTitle)");
                parameters = new SqlParameter[]  {
                    new SqlParameter("@ID", SqlDbType.VarChar, 25),
                    new SqlParameter("@BID", SqlDbType.VarChar, 25),
                    new SqlParameter("@FrameCode", SqlDbType.VarChar, 30),
                    new SqlParameter("@Price", SqlDbType.Decimal, 9),
                    new SqlParameter("@PriceJM", SqlDbType.Decimal, 9),
                    new SqlParameter("@InvTitle", SqlDbType.NVarChar, 50)
                };
                parameters[0].Value = "";
                parameters[1].Value = BID;
                parameters[2].Value = dr["FrameCode"].ToString();
                parameters[3].Value = dr["Price"].ToString();
                parameters[4].Value = dr["PriceJM"].ToString();
                parameters[5].Value = dr["InvTitle"].ToString();

                strSql.Append("--" + i++.ToString());
                htstrSqlSub.Add(strSql.ToString(), parameters);
                strSql.Clear();
            }
            DbHelperSQL u = new DbHelperSQL(dbCode);

            u.ExecuteSqlTran(htstrSqlMain, htstrSqlSub);

            return(htstrSqlSub.Count);
        }
        public int Import(string dbCode, int lgIndex, string fileName, string BID)
        {
            DbHelperOledb oledb = new DbHelperOledb();
            DataSet       ds    = oledb.ChangeExcelToDataSet(fileName);

            Hashtable htstrSqlMain = new Hashtable();
            Hashtable htstrSqlSub  = new Hashtable();

            SqlParameter[] parameters = null;
            StringBuilder  strSql     = new StringBuilder();

            strSql.Append("Delete Sale_PriceContract_CusGroup_CusCode where GpCode=@GpCode ;");
            parameters = new SqlParameter[] {
                new SqlParameter("@GpCode", SqlDbType.VarChar, 22)
            };
            parameters[0].Value = BID;
            htstrSqlMain.Add(strSql.ToString(), parameters);
            strSql.Clear();

            int i = 1;

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                strSql.Append("insert into Sale_PriceContract_CusGroup_CusCode(");
                strSql.Append("GpCode,CusCode)");
                strSql.Append(" values (@GpCode,@CusCode);");
                parameters = new SqlParameter[] {
                    new SqlParameter("@GpCode", SqlDbType.NVarChar, 30),
                    new SqlParameter("@CusCode", SqlDbType.NVarChar, 10)
                };
                parameters[0].Value = dr["GpCode"].ToString();
                parameters[1].Value = dr["CusCode"].ToString();

                strSql.Append("--" + i++.ToString());
                htstrSqlSub.Add(strSql.ToString(), parameters);
                strSql.Clear();
            }
            DbHelperSQL u = new DbHelperSQL(dbCode);

            u.ExecuteSqlTran(htstrSqlMain, htstrSqlSub);

            return(htstrSqlSub.Count);
        }
Ejemplo n.º 5
0
        public int Import(string dbCode, int lgIndex, string fileName)
        {
            DbHelperOledb oledb = new DbHelperOledb();
            DataSet       ds    = oledb.ChangeExcelToDataSet(fileName);

            Hashtable htstrSql = new Hashtable();

            SqlParameter[] parameters = null;
            StringBuilder  strSql     = new StringBuilder();

            int i = 1;

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                strSql.Append("Delete B_Process where ProcessCode=@ProcessCode and ProcessName=@ProcessName;");
                strSql.Append("insert into B_Process(");
                strSql.Append("ProcessCode,ProcessName,Remark,CF_Flag,KF_Flag)");
                strSql.Append(" values (");
                strSql.Append("@ProcessCode,@ProcessName,@Remark,@CF_Flag,@KF_Flag)");
                parameters = new SqlParameter[] {
                    new SqlParameter("@ProcessCode", SqlDbType.NVarChar, 20),
                    new SqlParameter("@ProcessName", SqlDbType.NVarChar, 50),
                    new SqlParameter("@Remark", SqlDbType.NVarChar, 100),
                    new SqlParameter("@CF_Flag", SqlDbType.Bit, 1),
                    new SqlParameter("@KF_Flag", SqlDbType.Bit, 1)
                };
                parameters[0].Value = dr["ProcessCode"].ToString().Trim();
                parameters[1].Value = dr["ProcessName"].ToString().Trim();
                parameters[2].Value = dr["Remark"].ToString().Trim();
                parameters[3].Value = Convert.ToBoolean(dr["CF_Flag"].ToString() == "" ? false : dr["CF_Flag"]);
                parameters[4].Value = Convert.ToBoolean(dr["KF_Flag"].ToString() == "" ? false : dr["KF_Flag"]);

                strSql.Append("--" + i++.ToString());
                htstrSql.Add(strSql.ToString(), parameters);
                strSql.Clear();
            }
            DbHelperSQL u = new DbHelperSQL(dbCode);

            u.ExecuteSqlTran(htstrSql);
            return(htstrSql.Count);
        }
Ejemplo n.º 6
0
        private void CreateMdb(string mdb)
        {
            string  sSql;
            Catalog catalog = new Catalog();

            try
            {
                string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdb;
                catalog.Create(connectionString);
                if (null != catalog.ActiveConnection)
                {
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog.ActiveConnection);
                }
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog);
                sSql  = @"CREATE TABLE [VTable]";
                sSql += @"([SEQUENCE_NUM] char(255) , [ORDER_NO] char(255) NOT NULL,";
                sSql += @"[OUT_GOOD] char(50),[OUT_GOODTIME] char(50), [RLENS_SIZE] int, [RLENS_TYPE] char(30),";
                sSql += @"[RSPH] int, [RCYL] int, [RAXIS] char(50) , [RADDS] int,";
                sSql += @"[RQTY] decimal(18, 6) NULL, [LLENS_SIZE] int NULL, [LLENS_TYPE] varchar(30) NULL,";
                sSql += @"[LSPH] int NULL, [LCYL] int NULL, [LAXIS] nvarchar(50) NULL, [LADDS] int NULL,";
                sSql += @"[LQTY] decimal(18, 6) NULL, [TINT_CODE] char(10) NULL, [TINT_CONTENT] char(10) NULL,";
                sSql += @"[UV] bit NULL, [HARD] bit NULL, [COATING] nvarchar(50) NULL, [BR] nchar(10) NULL,";
                sSql += @"[MIR] char(10) NULL, [RCP] nvarchar(50) NULL, [LCP] nvarchar(50) NULL, [RBASE] nvarchar(50) NULL,";
                sSql += @"[LBASE] nvarchar(50) NULL, [RKNIFE_SIDER] bit NULL, [LKNIFE_SIDER] bit NULL,";
                sSql += @"[RDECENTER] nvarchar(50) NULL, [LDECENTER] nvarchar(50) NULL, [RPRISM] nvarchar(50) NULL,";
                sSql += @"[LPRISM] nvarchar(50) NULL, [INPUT_DATE] datetime NOT NULL, [OUTPUT_DATE] datetime NULL,";
                sSql += @"[PRINTED] bit NULL,[PRINT_BAG] bit NULL,  [REMARK] varchar(255) NULL,";
                sSql += @"[LENS_MATERIAL] char(10) NULL, [BarCode] char(30) NULL,";
                sSql += @"[PD] char(30) NULL, [POLISH] char(30) NULL,";
                sSql += @"[AURO] char(30) NULL, [EDGE] char(30) NULL,";
                sSql += @"[SUPER] char(30) NULL, [SPECIAL] char(30) NULL, [SPECIAL_CODE] char(30) NULL";
                sSql += @") ";
                DbHelperOledb.ExecuteSql(sSql, connectionString);
            }
            catch
            {
                throw;
            }
        }
Ejemplo n.º 7
0
        private void PrepareExportToExcel(string dbCode, int lgIndex, string mdbFile, string excelFileName, string totleBillcode)
        {
            string sSql             = "delete from [VTable]";
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbFile + ";Persist Security Info=True";

            DbHelperOledb.ExecuteSql(sSql, connectionString);
            sSql = @"  
            select
            S1.SN  as SEQUENCE_NUM,S1.BCode as ORDER_NO,DAY(S1.BDate) as OUT_GOOD,'' as OUT_GOODTIME,
            S1.DiaR as RLENS_SIZE,S1.LensCodeRR as RLENS_TYPE,SPHR as RSPH,CYLR as RCYL,S1.AxisR as RAXIS,X_ADDR as RADDS, QtyR as RQTY,
            S1.DiaL as LLENS_SIZE,S1.LensCodeRL as LLENS_TYPE,SPHL as LSPH,CYLL as LCYL,S1.AxisL as LAXIS,X_ADDL as LADDS,QtyL as LQTY,
            S1.RSName as TINT_CODE,S1.RS as TINT_CONTENT,S1.UV,S1.JY as HARD,S1.JS as COATING,S1.CS as BR,S1.SY as MIR,
            S1.CTR as RCP,S1.CTL as LCP,S1.BASER as RBASE,S1.BASEL as LBASE,S1.DBR as RKNIFE_SIDER,S1.DBL as LKNIFE_SIDER,
            rtrim(ltrim(S1.D1R))+rtrim(ltrim(S1.D2R))+rtrim(ltrim(S1.D3R))+rtrim(ltrim(S1.D4R)) as RDECENTER,
            rtrim(ltrim(S1.D1L))+rtrim(ltrim(S1.D2L))+rtrim(ltrim(S1.D3L))+rtrim(ltrim(S1.D4L)) as LDECENTER,
            rtrim(ltrim(S1.P1R))+rtrim(ltrim(S1.P2R))+rtrim(ltrim(S1.P3R))+rtrim(ltrim(S1.P4R)) as RPRISM,
            rtrim(ltrim(S1.P1L))+rtrim(ltrim(S1.P2L))+rtrim(ltrim(S1.P3L))+rtrim(ltrim(S1.P4L)) as LPRISM,
            CONVERT(varchar(100), BDate , 23) as INPUT_DATE,CONVERT(varchar(100), GETDATE() , 23) OUTPUT_DATE,0 PRINTED,0 PRINT_BAG,
            '['+rtrim(ltrim(S1.BCode))+']'+';'+'[PD:'+PD+'];'+rtrim(ltrim(Notes)) as TBZM,
            isnull((select top 1 B1.Materials from B_Material_Lens B1 with (nolock) where B1.LensCode=S1.LensCodeRR),'') LENS_MATERIAL,
            isnull((select top 1 BCode+right(rtrim(ltrim(S1.OBCode)),5) from B_Material_Lens B1 with(nolock) where substring(BCode,4,12)=substring(S1.OBCode,1,4)),'') as Barcode,
            PD,S1.PG as POLISH,S1.CB as AURO,S1.ChB as EDGE,S1.KK as SUPER,S1.PH as SPECIAL,'' as SPECIAL_CODE
            from  
            V_Sale_Order_SD S1 
            where  S1.ID in (" + totleBillcode + @") order by S1.BCode";

            DbHelperSQL db = new DbHelperSQL(dbCode);
            DataSet     ds = db.Query(sSql);
            DataTable   dt = ds.Tables[0];

            foreach (DataRow dr in dt.Rows)
            {
                if (dr["RAXIS"].ToString() == "")
                {
                    dr["RAXIS"] = "0";
                }
                if (dr["LAXIS"].ToString() == "")
                {
                    dr["LAXIS"] = "0";
                }
                if (Convert.ToInt32(dr["RQTY"]) <= 0)
                {
                    dr["RSPH"]  = "0";
                    dr["RCYL"]  = "0";
                    dr["RAXIS"] = "0";
                }
                if (Convert.ToInt32(dr["LQTY"]) <= 0)
                {
                    dr["LSPH"]  = "0";
                    dr["LCYL"]  = "0";
                    dr["LAXIS"] = "0";
                }
                sSql  = "Insert  into [VTable] values ('" + dr["ORDER_NO"].ToString().Trim() + "','" + dr["SEQUENCE_NUM"].ToString().Trim() + _add + "','";
                sSql += dr["OUT_GOOD"].ToString().Trim() + "','" + dr["OUT_GOODTIME"].ToString().Trim() + "'," + (dr["RLENS_SIZE"].ToString().Trim() == "" ? "0" : dr["RLENS_SIZE"].ToString().Trim()).Trim() + ",'";
                sSql += dr["RLENS_TYPE"].ToString().Trim() + "'," + dr["RSPH"].ToString().Trim() + "," + dr["RCYL"].ToString().Trim() + ",'" + dr["RAXIS"].ToString().Trim() + "',";
                sSql += dr["RADDS"].ToString().Trim() + "," + Convert.ToInt32(dr["RQTY"]) + "," + (dr["LLENS_SIZE"].ToString().Trim() == "" ? "0" : dr["LLENS_SIZE"].ToString().Trim()).Trim() + ",'" + dr["LLENS_TYPE"].ToString().Trim() + "',";
                sSql += dr["LSPH"].ToString().Trim() + "," + dr["LCYL"].ToString().Trim() + ",'" + dr["LAXIS"].ToString().Trim() + "'," + dr["LADDS"].ToString().Trim() + ",";
                sSql += Convert.ToInt32(dr["LQTY"]) + ",'" + dr["TINT_CODE"].ToString().Trim() + "','" + dr["TINT_CONTENT"].ToString().Trim() + "'," + Convert.ToByte(dr["UV"]) + ",";
                sSql += Convert.ToByte(dr["HARD"]) + ",'" + dr["COATING"].ToString().Trim() + "','" + dr["BR"].ToString().Trim() + "','" + dr["MIR"].ToString().Trim() + "','";
                sSql += dr["RCP"].ToString().Trim() + "','" + dr["LCP"].ToString().Trim() + "','" + dr["RBASE"].ToString().Trim() + "','" + dr["LBASE"].ToString().Trim() + "',";
                sSql += Convert.ToByte(dr["RKNIFE_SIDER"]) + "," + Convert.ToByte(dr["LKNIFE_SIDER"]) + ",'" + dr["RDECENTER"].ToString().Trim() + "','" + dr["LDECENTER"].ToString().Trim() + "','";
                sSql += dr["RPRISM"].ToString().Trim() + "','" + dr["LPRISM"].ToString().Trim() + "','" + dr["INPUT_DATE"].ToString().Trim() + "','" + dr["OUTPUT_DATE"].ToString().Trim() + "',";
                sSql += Convert.ToByte(dr["PRINTED"]) + "," + Convert.ToByte(dr["PRINT_BAG"]) + ",'" + dr["TBZM"].ToString().Trim() + "','" + dr["LENS_MATERIAL"].ToString().Trim() + "','" + dr["Barcode"].ToString().Trim() + "','";
                sSql += dr["PD"].ToString().Trim() + "','" + dr["POLISH"].ToString().Trim() + "','" + dr["AURO"].ToString().Trim() + "','" + dr["EDGE"].ToString().Trim() + "','" + dr["SUPER"].ToString().Trim() + "','";
                sSql += dr["SPECIAL"].ToString().Trim() + "','')";
                DbHelperOledb.ExecuteSql(sSql, connectionString);
            }

            this.ExportToExcel(mdbFile, excelFileName);
        }
Ejemplo n.º 8
0
        public int Import(string dbCode, int lgIndex, string fileName)
        {
            DbHelperOledb oledb = new DbHelperOledb();
            DataSet       ds    = oledb.ChangeExcelToDataSet(fileName);

            Hashtable htstrSql = new Hashtable();

            SqlParameter[] parameters = null;
            StringBuilder  strSql     = new StringBuilder();

            int i = 1;

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                strSql.Append("insert into B_ProcessControl(");
                strSql.Append("SubID,Mnumber,JuSe,ChaSe,ShuiYin,RanSe,UV,JingJia,Hardened,PaoGuang,CaiBian,CheBian,KaiKeng,PiHua,DaoBian,MianWan,ZuanKong,OtherProcess,ExtraProcess,IsForce)");
                strSql.Append(" values (");
                strSql.Append("@SubID,@Mnumber,@JuSe,@ChaSe,@ShuiYin,@RanSe,@UV,@JingJia,@Hardened,@PaoGuang,@CaiBian,@CheBian,@KaiKeng,@PiHua,@DaoBian,@MianWan,@ZuanKong,@OtherProcess,@ExtraProcess,@IsForce)");
                parameters = new SqlParameter[] {
                    new SqlParameter("@SubID", SqlDbType.Int, 4),
                    new SqlParameter("@Mnumber", SqlDbType.NVarChar, 50),
                    new SqlParameter("@JuSe", SqlDbType.NVarChar, 30),
                    new SqlParameter("@ChaSe", SqlDbType.NVarChar, 30),
                    new SqlParameter("@ShuiYin", SqlDbType.NVarChar, 30),
                    new SqlParameter("@RanSe", SqlDbType.NVarChar, 30),
                    new SqlParameter("@UV", SqlDbType.Bit, 1),
                    new SqlParameter("@JingJia", SqlDbType.NVarChar, 30),
                    new SqlParameter("@Hardened", SqlDbType.Bit, 1),
                    new SqlParameter("@PaoGuang", SqlDbType.NVarChar, 30),
                    new SqlParameter("@CaiBian", SqlDbType.NVarChar, 30),
                    new SqlParameter("@CheBian", SqlDbType.NVarChar, 30),
                    new SqlParameter("@KaiKeng", SqlDbType.NVarChar, 30),
                    new SqlParameter("@PiHua", SqlDbType.NVarChar, 30),
                    new SqlParameter("@DaoBian", SqlDbType.NVarChar, 30),
                    new SqlParameter("@MianWan", SqlDbType.NVarChar, 30),
                    new SqlParameter("@ZuanKong", SqlDbType.NVarChar, 30),
                    new SqlParameter("@OtherProcess", SqlDbType.NVarChar, 30),
                    new SqlParameter("@ExtraProcess", SqlDbType.NVarChar, 30),
                    new SqlParameter("@IsForce", SqlDbType.Bit, 1)
                };
                parameters[0].Value  = --i;
                parameters[1].Value  = dr["Mnumber"].ToString().Trim();
                parameters[2].Value  = dr["JuSe"].ToString().Trim();
                parameters[3].Value  = dr["ChaSe"].ToString().Trim();
                parameters[4].Value  = dr["ShuiYin"].ToString().Trim();
                parameters[5].Value  = dr["RanSe"].ToString().Trim();
                parameters[6].Value  = Convert.ToBoolean(dr["UV"].ToString() == "" ? false : dr["UV"]);
                parameters[7].Value  = dr["JingJia"].ToString().Trim();
                parameters[8].Value  = Convert.ToBoolean(dr["Hardened"].ToString() == "" ? false : dr["Hardened"]);
                parameters[9].Value  = dr["PaoGuang"].ToString().Trim();
                parameters[10].Value = dr["CaiBian"].ToString().Trim();
                parameters[11].Value = dr["CheBian"].ToString().Trim();
                parameters[12].Value = dr["KaiKeng"].ToString().Trim();
                parameters[13].Value = dr["PiHua"].ToString().Trim();
                parameters[14].Value = dr["DaoBian"].ToString().Trim();
                parameters[15].Value = dr["MianWan"].ToString().Trim();
                parameters[16].Value = dr["ZuanKong"].ToString().Trim();
                parameters[17].Value = dr["OtherProcess"].ToString().Trim();
                parameters[18].Value = dr["ExtraProcess"].ToString().Trim();
                parameters[19].Value = Convert.ToBoolean(dr["IsForce"].ToString() == "" ? false : dr["IsForce"]);

                strSql.Append("--" + i.ToString());
                htstrSql.Add(strSql.ToString(), parameters);
                strSql.Clear();
            }
            DbHelperSQL u = new DbHelperSQL(dbCode);

            u.ExecuteSqlTran(htstrSql);

            strSql.Clear();

            strSql.Append(@"
            ;DELETE
            FROM  B_ProcessControl
            WHERE B_ProcessControl.%%physloc%%
                    NOT IN (SELECT MIN(b.%%physloc%%)
                            FROM   B_ProcessControl b
				                GROUP BY 
						            b.Mnumber,b.JuSe,b.ChaSe,b.ShuiYin,b.RanSe,b.UV,b.JingJia,
						            b.Hardened,b.PaoGuang,b.CaiBian,b.CheBian,b.KaiKeng,
						            b.PiHua,b.DaoBian,b.MianWan,b.ZuanKong,b.OtherProcess,
						            b.ExtraProcess,b.IsForce)"                        );

            strSql.Append(@";update B_ProcessControl
                                    set SubID=
                                    (select  N_ID from 
	                                    (select  ROW_NUMBER() OVER (ORDER BY Mnumber)  as N_ID,SubID
		                                    from B_ProcessControl s 
	                                    ) as a
	                                    where a.SubID=B_ProcessControl.SubID
                                    ) ");
            u.ExecuteSql(strSql.ToString());

            return(htstrSql.Count);
        }
Ejemplo n.º 9
0
        public int Import(string dbCode, int lgIndex, string fileName, string BID)
        {
            DbHelperOledb oledb = new DbHelperOledb();
            DataSet       ds    = oledb.ChangeExcelToDataSet(fileName);

            Hashtable htstrSqlMain = new Hashtable();
            Hashtable htstrSqlSub  = new Hashtable();

            SqlParameter[] parameters = null;
            StringBuilder  strSql     = new StringBuilder();

            strSql.Append("Delete Sale_PriceContract_Lens_ProCost where BID=@BID ;");
            parameters = new SqlParameter[] {
                new SqlParameter("@BID", SqlDbType.VarChar, 25)
            };
            parameters[0].Value = BID;
            htstrSqlMain.Add(strSql.ToString(), parameters);
            strSql.Clear();

            int i = 1;

            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                strSql.Append("delete Sale_PriceContract_Lens_ProCost ");
                strSql.Append("where BID=@BID and LensCode=@LensCode and F_Set=@F_Set ");
                strSql.Append("and JY=@JY and UV=@UV and JS=@JS and RS=@RS and CS=@CS  ");
                strSql.Append("and SY=@SY and CB=@CB and ChB=@ChB and KK=@KK and ZK=@ZK  ");
                strSql.Append("and PiH=@PiH and PG=@PG and JJ=@JJ and OP=@OP ;  ");
                ////////////////////////
                strSql.Append("insert into Sale_PriceContract_Lens_ProCost(");
                strSql.Append("ID,BID,LensCode,F_Set,InvTitle,JY,UV,JS,RS,CS,SY,CB,ChB,KK,ZK,PiH,PG,JJ,OP,P1,P2,P1JM,P2JM)");
                strSql.Append(" values (");
                strSql.Append("(select HKOERP.dbo.SF_GetID()),@BID,@LensCode,@F_Set,@InvTitle,@JY,@UV,@JS,@RS,@CS,@SY,@CB,@ChB,@KK,@ZK,@PiH,@PG,@JJ,@OP,@P1,@P2,@P1JM,@P2JM)");
                parameters = new SqlParameter[]  {
                    new SqlParameter("@ID", SqlDbType.VarChar, 25),
                    new SqlParameter("@BID", SqlDbType.VarChar, 25),
                    new SqlParameter("@LensCode", SqlDbType.VarChar, 30),
                    new SqlParameter("@F_Set", SqlDbType.Bit, 1),
                    new SqlParameter("@InvTitle", SqlDbType.NVarChar, 40),
                    new SqlParameter("@JY", SqlDbType.Bit, 1),
                    new SqlParameter("@UV", SqlDbType.Bit, 1),
                    new SqlParameter("@JS", SqlDbType.VarChar, 15),
                    new SqlParameter("@RS", SqlDbType.VarChar, 15),
                    new SqlParameter("@CS", SqlDbType.VarChar, 15),
                    new SqlParameter("@SY", SqlDbType.VarChar, 15),
                    new SqlParameter("@CB", SqlDbType.VarChar, 15),
                    new SqlParameter("@ChB", SqlDbType.VarChar, 15),
                    new SqlParameter("@KK", SqlDbType.VarChar, 15),
                    new SqlParameter("@ZK", SqlDbType.VarChar, 15),
                    new SqlParameter("@PiH", SqlDbType.VarChar, 15),
                    new SqlParameter("@PG", SqlDbType.VarChar, 15),
                    new SqlParameter("@JJ", SqlDbType.VarChar, 15),
                    new SqlParameter("@OP", SqlDbType.VarChar, 15),
                    new SqlParameter("@P1", SqlDbType.Decimal, 9),
                    new SqlParameter("@P2", SqlDbType.Decimal, 9),
                    new SqlParameter("@P1JM", SqlDbType.Decimal, 9),
                    new SqlParameter("@P2JM", SqlDbType.Decimal, 9)
                };
                parameters[0].Value  = "";
                parameters[1].Value  = BID;
                parameters[2].Value  = dr["LensCode"].ToString();
                parameters[3].Value  = System.Convert.ToBoolean(dr["F_Set"].ToString() == "" ? false : dr["F_Set"]);
                parameters[4].Value  = dr["InvTitle"].ToString();
                parameters[5].Value  = System.Convert.ToBoolean(dr["JY"].ToString() == "" ? false : dr["JY"]);
                parameters[6].Value  = System.Convert.ToBoolean(dr["UV"].ToString() == "" ? false : dr["UV"]);
                parameters[7].Value  = dr["JS"].ToString();
                parameters[8].Value  = dr["RS"].ToString();
                parameters[9].Value  = dr["CS"].ToString();
                parameters[10].Value = dr["SY"].ToString();
                parameters[11].Value = dr["CB"].ToString();
                parameters[12].Value = dr["ChB"].ToString();
                parameters[13].Value = dr["KK"].ToString();
                parameters[14].Value = dr["ZK"].ToString();
                parameters[15].Value = dr["PiH"].ToString();
                parameters[16].Value = dr["PG"].ToString();
                parameters[17].Value = dr["JJ"].ToString();
                parameters[18].Value = dr["OP"].ToString();
                parameters[19].Value = dr["P1"].ToString();
                parameters[20].Value = dr["P2"].ToString();
                parameters[21].Value = dr["P1JM"].ToString();
                parameters[25].Value = dr["P2JM"].ToString();

                strSql.Append("--" + i++.ToString());
                htstrSqlSub.Add(strSql.ToString(), parameters);
                strSql.Clear();
            }
            DbHelperSQL u = new DbHelperSQL(dbCode);

            u.ExecuteSqlTran(htstrSqlMain, htstrSqlSub);

            return(htstrSqlSub.Count);
        }