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