//查询detail是否存在
public List <SingleDetail> GetDetailList(SingleDetail singleDetail)
{
//构造要查询的sql语句
string sql = "select * from bg_cust_dec_detail_singlewindow where batch = '" + singleDetail.Batch + "' and cust_dec_head_id = '" + singleDetail.CustDecHeadId + "' and g_no = '" + singleDetail.GNo + "'";
//使用helper进行查询，得到结果
DataTable dt = mysqlHelper.GetDataTable(sql);
//将dt中的数据转存到list中
List <SingleDetail> list = new List <SingleDetail>();
foreach (DataRow row in dt.Rows)
{
list.Add(new SingleDetail()
{
Id = row["id"].ToString(),
CustDecHeadId = row["cust_dec_head_id"].ToString(),
Batch = row["batch"].ToString(),
});
}
//将集合返回
return(list);
}
public string InsertSingleDetail(SingleDetail mi)
{
//调用dal层的insert方法，完成插入操作
return(miDal.InsertSingleDetail(mi));
}
//查询detail是否存在
public bool UpdateDetail(SingleDetail singleDetail)
{
//调用查询方法
return(miDal.UpdateDetail(singleDetail) > 0);
}
//查询detail是否存在
public List <SingleDetail> GetDetailList(SingleDetail singleDetail)
{
//调用查询方法
return(miDal.GetDetailList(singleDetail));
}
/// <summary>
/// 插入数据
/// </summary>
/// <param name="mi">ManagerInfo类型的对象</param>
/// <returns></returns>
public string InsertSingleDetail(SingleDetail mi)
{
//构造insert语句
string sql = @"insert into bg_cust_dec_detail_singlewindow(g_no,custom_code,append_code,cust_dec_head_id,batch,ciq_code,ciq_name,goods_name,spec,";
sql += "valuation_qty,valuation_unit_code,valuation_unit_name,unit_price,unit_total_price,currency_code,currency_name,";
sql += "legal_qty_1,legal_unit_1_code,legal_unit_1_name,destination_country_std,destination_country_std_name,legal_qty_2,legal_unit_2_code,legal_unit_2_name,";
sql += "origin_country_std,origin_country_std_name,district_code,district_name,dest_code,dest_name,nc_detail_code,nc_detail_name,goods_spec,goods_attr,";
sql += "goods_attr_name,purpose,purpose_name,trade_curr_std,gross_weight,net_weight,goods_brand,prod_batch_no,country_of_origin_code,country_of_origin_name,create_date,update_date)";
sql += @" values(@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,
@p23,@p24,@p25,@p26,@p27,@p28,@p29,@p30,@p31,@p32,@p33,@p34,@p35,@p36,@p37,@p38,@p39,@p40,@p41,@p42,@p43,@p44,@p45);SELECT @@Identity;";
//构造sql语句的参数
MySqlParameter[] ps = //使用数组初始化器
{
new MySqlParameter("@p0", mi.GNo),
new MySqlParameter("@p1", mi.CustomCode),
new MySqlParameter("@p2", mi.AppendCode),
new MySqlParameter("@p3", mi.CustDecHeadId),
new MySqlParameter("@p4", mi.Batch),
new MySqlParameter("@p5", mi.CiqCode),
new MySqlParameter("@p6", mi.CiqName),
new MySqlParameter("@p7", mi.GoodsName),
new MySqlParameter("@p8", mi.Spec),
new MySqlParameter("@p9", mi.ValuationQty),
new MySqlParameter("@p10", mi.ValuationUnitCode),
new MySqlParameter("@p11", mi.ValuationUnitName),
new MySqlParameter("@p12", mi.UnitPrice),
new MySqlParameter("@p13", mi.UnitTotalPrice),
new MySqlParameter("@p14", mi.CurrencyCode),
new MySqlParameter("@p15", mi.CurrencyName),
new MySqlParameter("@p16", mi.LegalQty1),
new MySqlParameter("@p17", mi.LegalUnit1Code),
new MySqlParameter("@p18", mi.LegalUnit1Name),
new MySqlParameter("@p19", mi.DestinationCountryStd),
new MySqlParameter("@p20", mi.DestinationCountryStdName),
new MySqlParameter("@p21", mi.LegalQty2),
new MySqlParameter("@p22", mi.LegalUnit2Code),
new MySqlParameter("@p23", mi.LegalUnit2Name),
new MySqlParameter("@p24", mi.OriginCountryStd),
new MySqlParameter("@p25", mi.OriginCountryStdName),
new MySqlParameter("@p26", mi.DistrictCode),
new MySqlParameter("@p27", mi.DistrictName),
new MySqlParameter("@p28", mi.DestCode),
new MySqlParameter("@p29", mi.DestName),
new MySqlParameter("@p30", mi.NcDetailCode),
new MySqlParameter("@p31", mi.NcDetailName),
new MySqlParameter("@p32", mi.GoodsSpec),
new MySqlParameter("@p33", mi.GoodsAttr),
new MySqlParameter("@p34", mi.GoodsAttrName),
new MySqlParameter("@p35", mi.Purpose),
new MySqlParameter("@p36", mi.PurposeName),
new MySqlParameter("@p37", mi.TradeCurrStd),
new MySqlParameter("@p38", mi.GrossWeight),
new MySqlParameter("@p39", mi.NetWeight),
new MySqlParameter("@p40", mi.GoodsBrand),
new MySqlParameter("@p41", mi.ProdBatchNo),
new MySqlParameter("@p42", mi.CountryOfOriginCode),
new MySqlParameter("@p43", mi.CountryOfOriginName),
new MySqlParameter("@p44", mi.CreateDate),
new MySqlParameter("@p45", mi.UpdateDate)
};
//执行插入操作
return(mysqlHelper.ExecuteScalar(sql, ps).ToString());
}
//更新detail
public int UpdateDetail(SingleDetail mi)
{
//构造要查询的sql语句
string sql = @"update bg_cust_dec_detail_singlewindow set
g_no=@p0,custom_code=@p1, append_code=@p2, cust_dec_head_id=@p3, batch=@p4, ciq_code=@p5, ciq_name=@p6, goods_name=@p7, spec=@p8,
valuation_qty=@p9 ,valuation_unit_code =@p10 , valuation_unit_name=@p11 ,unit_price =@p12 ,unit_total_price=@p13 ,currency_code =@p14 ,currency_name=@p15 ,
legal_qty_1=@p16 ,legal_unit_1_code =@p17 ,legal_unit_1_name=@p18 ,destination_country_std =@p19 ,destination_country_std_name=@p20 ,legal_qty_2 =@p21 ,legal_unit_2_code=@p22 ,legal_unit_2_name=@p23 ,
origin_country_std=@p24 ,origin_country_std_name =@p25 ,district_code=@p26 ,district_name =@p27 ,dest_code=@p28 ,dest_name=@p29 ,nc_detail_code =@p30 ,nc_detail_name=@p31 ,goods_spec =@p32 ,goods_attr=@p33 ,
goods_attr_name=@p34 ,purpose =@p35 ,purpose_name=@p36 ,trade_curr_std =@p37 ,gross_weight=@p38 ,net_weight =@p39 ,goods_brand=@p40 ,prod_batch_no =@p41 ,country_of_origin_code=@p42 ,country_of_origin_name =@p43 ,car_ccc_no =@p44 ,update_date=@p45
where batch = '" + mi.Batch + "' and cust_dec_head_id = '" + mi.CustDecHeadId + "' and g_no = @p0 ";
//构造sql语句的参数
MySqlParameter[] ps = //使用数组初始化器
{
new MySqlParameter("@p0", mi.GNo),
new MySqlParameter("@p1", mi.CustomCode),
new MySqlParameter("@p2", mi.AppendCode),
new MySqlParameter("@p3", mi.CustDecHeadId),
new MySqlParameter("@p4", mi.Batch),
new MySqlParameter("@p5", mi.CiqCode),
new MySqlParameter("@p6", mi.CiqName),
new MySqlParameter("@p7", mi.GoodsName),
new MySqlParameter("@p8", mi.Spec),
new MySqlParameter("@p9", mi.ValuationQty),
new MySqlParameter("@p10", mi.ValuationUnitCode),
new MySqlParameter("@p11", mi.ValuationUnitName),
new MySqlParameter("@p12", mi.UnitPrice),
new MySqlParameter("@p13", mi.UnitTotalPrice),
new MySqlParameter("@p14", mi.CurrencyCode),
new MySqlParameter("@p15", mi.CurrencyName),
new MySqlParameter("@p16", mi.LegalQty1),
new MySqlParameter("@p17", mi.LegalUnit1Code),
new MySqlParameter("@p18", mi.LegalUnit1Name),
new MySqlParameter("@p19", mi.DestinationCountryStd),
new MySqlParameter("@p20", mi.DestinationCountryStdName),
new MySqlParameter("@p21", mi.LegalQty2),
new MySqlParameter("@p22", mi.LegalUnit2Code),
new MySqlParameter("@p23", mi.LegalUnit2Name),
new MySqlParameter("@p24", mi.OriginCountryStd),
new MySqlParameter("@p25", mi.OriginCountryStdName),
new MySqlParameter("@p26", mi.DistrictCode),
new MySqlParameter("@p27", mi.DistrictName),
new MySqlParameter("@p28", mi.DestCode),
new MySqlParameter("@p29", mi.DestName),
new MySqlParameter("@p30", mi.NcDetailCode),
new MySqlParameter("@p31", mi.NcDetailName),
new MySqlParameter("@p32", mi.GoodsSpec),
new MySqlParameter("@p33", mi.GoodsAttr),
new MySqlParameter("@p34", mi.GoodsAttrName),
new MySqlParameter("@p35", mi.Purpose),
new MySqlParameter("@p36", mi.PurposeName),
new MySqlParameter("@p37", mi.TradeCurrStd),
new MySqlParameter("@p38", mi.GrossWeight),
new MySqlParameter("@p39", mi.NetWeight),
new MySqlParameter("@p40", mi.GoodsBrand),
new MySqlParameter("@p41", mi.ProdBatchNo),
new MySqlParameter("@p42", mi.CountryOfOriginCode),
new MySqlParameter("@p43", mi.CountryOfOriginName),
new MySqlParameter("@p44", mi.CarCccNo),
new MySqlParameter("@p45", mi.UpdateDate)
};
//执行语句并返回结果
return(mysqlHelper.ExcuteNonQuery(sql, ps.ToArray()));
}
