public ProductItemMgr(string connectionStr)
{
this.connectionStr = connectionStr;
_productItemDao = new ProductItemDao(connectionStr);
_serialMgr = new SerialMgr(connectionStr);
productItemDao = new ProductItemDao(connectionStr);
_functionMgr = new FunctionMgr(connectionStr);
}
public ProductItemMapExcelMgr(string connectionString)
{
_productItemMapMgr = new ProductItemMapMgr(connectionString);
_productItemMapDao = new ProductItemMapDao(connectionString);
_productItemMgr = new ProductItemMgr(connectionString);
_productItemDao = new ProductItemDao(connectionString);
_callerMgr = new CallerMgr(connectionString);
_priceMasterMgr = new PriceMasterMgr(connectionString);
_access = DBAccess.DBFactory.getDBAccess(DBType.MySql, connectionString);
}
public int Vendor_TempMove2Pro(int writerId, int combo_type, string product_Id, ProductTemp pt)
{
ArrayList sqls = new ArrayList();
int product_id = 0;
ProductTempMgr proTempMgr = new ProductTempMgr("");
ProductTemp proTemp = new ProductTemp { Combo_Type = combo_type, Product_Id = product_Id, Create_Channel = 2 };//1:後台管理者(manage_user) edit by xiagnwang0413w 2014/08/09
string movePro = proTempMgr.Vendor_MoveProduct(proTemp);
sqls.Add(proTempMgr.Vendor_Delete(proTemp));
ProductNoticeSetTempMgr proNoticeSetTempMgr = new ProductNoticeSetTempMgr("");
ProductNoticeSetTemp proNoticeSetTemp = new ProductNoticeSetTemp { Combo_Type = combo_type, product_id = product_Id };
sqls.Add(proNoticeSetTempMgr.Vendor_MoveNotice(proNoticeSetTemp));
sqls.Add(proNoticeSetTempMgr.Vendor_Delete(proNoticeSetTemp));
ProductTagSetTempMgr proTagSetTempMgr = new ProductTagSetTempMgr("");
ProductTagSetTemp proTagSetTemp = new ProductTagSetTemp { Combo_Type = combo_type, product_id = product_Id };
sqls.Add(proTagSetTempMgr.Vendor_MoveTag(proTagSetTemp));
sqls.Add(proTagSetTempMgr.Vendor_Delete(proTagSetTemp));
ProductPictureTempImplMgr proPicTempMgr = new ProductPictureTempImplMgr("");
ProductPictureTemp proPictureTemp = new ProductPictureTemp { combo_type = combo_type, product_id = product_Id };
sqls.Add(proPicTempMgr.Vendor_MoveToProductPicture(proPictureTemp));
sqls.Add(proPicTempMgr.Vendor_Delete(proPictureTemp));
ProductCategorySetTempMgr proCateSetTempMgr = new ProductCategorySetTempMgr("");
ProductCategorySetTemp proCategorySetTemp = new ProductCategorySetTemp { Combo_Type = combo_type, Product_Id = product_Id.ToString() };
sqls.Add(proCateSetTempMgr.Vendor_TempMoveCategory(proCategorySetTemp));
sqls.Add(proCateSetTempMgr.Vendor_TempDelete(proCategorySetTemp));
//product_status_history.type 1,申請審核 2,核可 3,駁回 4,下架 5,新建商品 6,上架 7,系統移轉建立 8,取消送審 parametertype='verify_operate_type'
//product_status_history.product_status 0,新建立商品 1,申請審核 2,審核通過 5,上架 6,下架 20,供應商新建商品 parametertype='product_status'
ProductStatusHistoryMgr proStatusHistoryMgr = new ProductStatusHistoryMgr("");
sqls.Add(proStatusHistoryMgr.SaveNoProductId(new ProductStatusHistory { product_status = 20, user_id = Convert.ToUInt32(pt.Writer_Id), type = 5 }));//供應商新建立商品
sqls.Add(proStatusHistoryMgr.SaveNoProductId(new ProductStatusHistory { product_status = 0, user_id = Convert.ToUInt32(writerId), type = 2 })); //管理員核可
ItemPriceTempMgr itemTempPriceMgr = new ItemPriceTempMgr("");
sqls.Add(itemTempPriceMgr.Vendor_Delete(product_Id, combo_type, 0));
PriceMasterTempMgr priceMasterTempMgr = new PriceMasterTempMgr("");
PriceMasterTemp priceMasterTemp = new PriceMasterTemp { product_id = product_Id, combo_type = combo_type };
sqls.Add(priceMasterTempMgr.Vendor_Delete(priceMasterTemp));
//判斷是單一商品還是組合商品
if (combo_type == 1)
{//單一商品
IProductItemImplDao piDao = new ProductItemDao(connectionStr);
ProductItemTempMgr proItemTempMgr = new ProductItemTempMgr("");
ProductItemTemp proItemTemp = new ProductItemTemp { Product_Id = product_Id };
string selItem = proItemTempMgr.VendorQuerySql(proItemTemp);
string moveItem = proItemTempMgr.VendorMoveProductItem(proItemTemp);//方法修改了writerId
sqls.Add(proItemTempMgr.DeleteVendorSql(proItemTemp));
ProductSpecTempMgr proSpecTempMgr = new ProductSpecTempMgr("");
ProductSpecTemp proSpecTemp = new ProductSpecTemp { product_id = product_Id };
sqls.Add(proSpecTempMgr.VendorTempMoveSpec(proSpecTemp));
sqls.Add(proSpecTempMgr.VendorTempDelete(proSpecTemp));
string priceMaster = priceMasterTempMgr.VendorMove2PriceMaster(priceMasterTemp);
ItemPriceMgr itemPriceMgr = new ItemPriceMgr("");
string itemPrice = itemPriceMgr.SaveFromItem(pt.Writer_Id, pt.Product_Id);
// add 處理price_mater_temp and product_combo_temp
sqls.Add(proTempMgr.VendorEditCM(proTemp));
product_id = _productDao.TempMove2Pro(movePro, "", moveItem, "", selItem, priceMaster, itemPrice, sqls);
if (product_id > 0)
{
piDao.UpdateErpId(product_id.ToString());
}
return product_id;
}
else
{//組合商品
ProductComboTempMgr pcTempMgr = new ProductComboTempMgr("");
ProductComboTemp proComboTemp = new ProductComboTemp { Parent_Id = product_Id };
sqls.Add(pcTempMgr.Vendor_TempMoveCombo(proComboTemp));
sqls.Add(pcTempMgr.TempDeleteByVendor(proComboTemp));
string selPrice = priceMasterTempMgr.SelectChild(priceMasterTemp);
string priceMaster = priceMasterTempMgr.VendorMove2PriceMaster(priceMasterTemp);
ItemPriceTempMgr itemPriceTempMgr = new ItemPriceTempMgr("");
string itemPrice = itemPriceTempMgr.VendorMove2ItemPrice();
sqls.Add(itemPriceTempMgr.Vendor_Delete(product_Id, combo_type, 0));
return _productDao.TempMove2Pro(movePro, "", "", "", selPrice, priceMaster, itemPrice, sqls);
}
//記錄的sql語句
//insert into product(product_id,brand_id,product_vendor_code,product_name,product_price_list,product_spec,spec_title_1,spec_title_2,product_freight_set,product_buy_limit,product_status,product_hide,product_mode,product_sort,product_start,product_end,page_content_1,page_content_2,page_content_3,product_keywords,product_recommend,product_password,product_total_click,expect_time,product_image,product_createdate,product_updatedate,product_ipfrom,goods_area,goods_image1,goods_image2,city,bag_check_money,combination,bonus_percent,default_bonus_percent,bonus_percent_start,bonus_percent_end,tax_type,cate_id,fortune_quota,fortune_freight,product_media,ignore_stock,shortage,stock_alarm,price_type,user_id,show_listprice,expect_msg,create_channel ) select 15164 as product_id,brand_id,product_vendor_code,product_name,product_price_list,product_spec,spec_title_1,spec_title_2,product_freight_set,product_buy_limit,'0' as product_status,product_hide,product_mode,product_sort,product_start,product_end,page_content_1,page_content_2,page_content_3,product_keywords,product_recommend,product_password,product_total_click,expect_time,product_image,1411033321 as product_createdate,product_updatedate,product_ipfrom,goods_area,goods_image1,goods_image2,city,bag_check_money,combination,bonus_percent,default_bonus_percent,bonus_percent_start,bonus_percent_end,tax_type,cate_id,fortune_quota,fortune_freight,product_media,ignore_stock,shortage,stock_alarm,price_type,writer_id,show_listprice,expect_msg,create_channel from product_temp where 1=1 and combo_type=2 and create_channel=2 and product_id='T578';
//select price_master_id,product_id,child_id from price_master_temp where 1=1 and combo_type=2 and product_id='T578';
//insert into price_master(`product_id`,`site_id`,`user_level`,`user_id`,`product_name`,`accumulated_bonus`,`bonus_percent`,`default_bonus_percent`,`same_price`,`event_start`,`event_end`,`price_status`,`price`,`event_price`,`child_id`,`cost`,`event_cost`,`bonus_percent_start`,`bonus_percent_end`,`max_price`,`max_event_price`,`valid_start`,`valid_end`)
//select 15164 as product_id,site_id,user_level,user_id,product_name,accumulated_bonus,bonus_percent,default_bonus_percent,same_price,event_start,event_end,price_status,price,event_price,15164 as child_id,cost,event_cost,bonus_percent_start,bonus_percent_end,max_price,max_event_price,valid_start,valid_end from price_master_temp where 1=1 and combo_type=2 and product_id='T578';select @@identity;
//insert into item_price(`price_master_id`,`item_id`,`item_money`,`item_cost`,`event_money`,`event_cost`) select 5699 as price_master_id,item_id,item_money,item_cost,event_money,event_cost from item_price_temp where price_master_id=1751
//set sql_safe_updates = 0;update price_master set child_id=15164 where price_master_id=5699; set sql_safe_updates = 1;
//set sql_safe_updates = 0;delete from product_temp where product_id='T578';set sql_safe_updates = 1
//insert into product_notice_set(product_id,notice_id) select 15164 as product_id,notice_id from product_notice_set_temp where 1=1 and combo_type = 2 and product_id='T578';
//set sql_safe_updates=0;delete from product_notice_set_temp where product_id='T578';set sql_safe_updates=1;
//insert into product_tag_set(`product_id`,`tag_id`) select 15164 as product_id,tag_id from product_tag_set_temp where 1=1 and combo_type=2 and product_id='T578';
//set sql_safe_updates=0;delete from product_tag_set_temp where product_id='T578';set sql_safe_updates=1;
//insert into product_picture(product_id,image_filename,image_sort,image_state,image_createdate) select 15164 as product_id,image_filename,image_sort,image_state,image_createdate from product_picture_temp where 1=1 and product_id='T578' and combo_type=2;
//set sql_safe_updates=0; delete from product_picture_temp where product_id='T578';set sql_safe_updates = 1;
//insert into product_category_set(product_id,category_id,brand_id) select 15164 as product_id,category_id,brand_id from product_category_set_temp where 1=1 and product_id='T578' and combo_type = 2
//set sql_safe_updates = 0; delete from product_category_set_temp where 1=1 and combo_type = 2 and product_id='T578';set sql_safe_updates = 1;
//set sql_safe_updates = 0; insert into product_status_history (`product_id`,`user_id`,`create_time`,`type`,`product_status`,`remark`) values (15164,0,now(),5,20,'');set sql_safe_updates = 1;
//set sql_safe_updates = 0; insert into product_status_history (`product_id`,`user_id`,`create_time`,`type`,`product_status`,`remark`) values (15164,122,now(),2,0,'');set sql_safe_updates = 1;
//set sql_safe_updates=0;delete item_price_temp from price_master_temp left join item_price_temp on item_price_temp.price_master_id=price_master_temp.price_master_id where 1=1 and price_master_temp.product_id='T578' and price_master_temp.combo_type=2 ;set sql_safe_updates=1;
//set sql_safe_updates = 0;delete from price_master_temp where 1=1 and product_id='T578' and combo_type=2;set sql_safe_updates = 1;
//insert into product_combo(`parent_id`,`child_id`,`s_must_buy`,`g_must_buy`,`pile_id`,`buy_limit`) select 15164 as parent_id,child_id,s_must_buy,g_must_buy,pile_id,buy_limit from product_combo_temp where 1=1 and parent_id='T578';
//set sql_safe_updates = 0; delete from product_combo_temp where writer_id = 0 and parent_id='T578'; set sql_safe_updates= 1;
//set sql_safe_updates=0;delete item_price_temp from price_master_temp left join item_price_temp on item_price_temp.price_master_id=price_master_temp.price_master_id where 1=1 and price_master_temp.product_id='T578' and price_master_temp.combo_type=2 ;set sql_safe_updates=1;
//SELECT * from product where product_id='15164'
}
public int TempMove2Pro(int writerId, int combo_type, string product_Id)
{
ArrayList sqls = new ArrayList();
int product_id = 0;
ProductTempMgr proTempMgr = new ProductTempMgr("");
ProductTemp proTemp = new ProductTemp { Writer_Id = writerId, Combo_Type = combo_type, Product_Id = product_Id, Create_Channel = 1 };//1:後台管理者(manage_user) edit by xiagnwang0413w 2014/08/09
string movePro = proTempMgr.MoveProduct(proTemp);
sqls.Add(proTempMgr.Delete(proTemp));
/*********start*********/
//將ProductDeliverySetTemp表數據導入正式表 edit by xiangwang0413w 2014/11/06
IProductDeliverySetTempImplMgr _proDelSetTemp = new ProductDeliverySetTempMgr("");
var proDelSetTemp = new ProductDeliverySetTemp { Writer_Id = writerId, Combo_Type = combo_type, Product_id = int.Parse(product_Id) };
sqls.Add(_proDelSetTemp.MoveProductDeliverySet(proDelSetTemp));
sqls.Add(_proDelSetTemp.Delete(proDelSetTemp));
/*******end***********/
ProductNoticeSetTempMgr proNoticeSetTempMgr = new ProductNoticeSetTempMgr("");
ProductNoticeSetTemp proNoticeSetTemp = new ProductNoticeSetTemp { Writer_Id = writerId, Combo_Type = combo_type, product_id = product_Id };
sqls.Add(proNoticeSetTempMgr.MoveNotice(proNoticeSetTemp));
sqls.Add(proNoticeSetTempMgr.Delete(proNoticeSetTemp));
ProductTagSetTempMgr proTagSetTempMgr = new ProductTagSetTempMgr("");
ProductTagSetTemp proTagSetTemp = new ProductTagSetTemp { Writer_Id = writerId, Combo_Type = combo_type, product_id = product_Id };
sqls.Add(proTagSetTempMgr.MoveTag(proTagSetTemp));
sqls.Add(proTagSetTempMgr.Delete(proTagSetTemp));
ProductPictureTempImplMgr proPicTempMgr = new ProductPictureTempImplMgr("");
ProductPictureTemp proPictureTemp = new ProductPictureTemp { writer_Id = writerId, combo_type = combo_type, product_id = product_Id };
sqls.Add(proPicTempMgr.MoveToProductPicture(proPictureTemp, 1));//更新說明圖表
sqls.Add(proPicTempMgr.MoveToProductPicture(proPictureTemp, 2));//更新APP圖表
sqls.Add(proPicTempMgr.Delete(proPictureTemp, 1));//刪除說明圖臨時表
sqls.Add(proPicTempMgr.Delete(proPictureTemp, 2)); //刪除app臨時表 add by wwei0216w 2014/11/11
ProductCategorySetTempMgr proCateSetTempMgr = new ProductCategorySetTempMgr("");
ProductCategorySetTemp proCategorySetTemp = new ProductCategorySetTemp { Writer_Id = writerId, Combo_Type = combo_type, Product_Id = product_Id.ToString() };
sqls.Add(proCateSetTempMgr.TempMoveCategory(proCategorySetTemp));
sqls.Add(proCateSetTempMgr.TempDelete(proCategorySetTemp));
ProductStatusHistoryMgr proStatusHistoryMgr = new ProductStatusHistoryMgr("");
sqls.Add(proStatusHistoryMgr.SaveNoProductId(new ProductStatusHistory { product_status = 0, user_id = Convert.ToUInt32(writerId), type = 5 }));
ItemPriceTempMgr itemTempPriceMgr = new ItemPriceTempMgr("");
sqls.Add(itemTempPriceMgr.Delete(product_Id, combo_type, writerId));
PriceMasterTempMgr priceMasterTempMgr = new PriceMasterTempMgr("");
PriceMasterTemp priceMasterTemp = new PriceMasterTemp { writer_Id = writerId, product_id = product_Id, combo_type = combo_type };
sqls.Add(priceMasterTempMgr.Delete(priceMasterTemp));
//判斷是單一商品還是組合商品
if (combo_type == 1)
{//單一商品
IProductItemImplDao piDao = new ProductItemDao(connectionStr);
ProductItemTempMgr proItemTempMgr = new ProductItemTempMgr("");
ProductItemTemp proItemTemp = new ProductItemTemp { Writer_Id = writerId, Product_Id = product_Id };
string selItem = proItemTempMgr.QuerySql(proItemTemp);
string moveItem = proItemTempMgr.MoveProductItem(proItemTemp);
sqls.Add(proItemTempMgr.DeleteSql(proItemTemp));
/*************start*課程相關*****************/
//CourseDetailItem
ICourseDetailItemTempImplMgr _cdItemMgr = new CourseDetailItemTempMgr("");
string moveCourDetaItem = _cdItemMgr.MoveCourseDetailItem(writerId);
sqls.Add(_cdItemMgr.DeleteSql(writerId));
//CourseProduct
ICourseProductTempImplMgr _courProdTempMgr = new CourseProductTempMgr("");
var courProdTemp = new CourseProductTemp { Writer_Id = writerId, Product_Id = uint.Parse(product_Id) };
string moveCourProd = _courProdTempMgr.MoveCourseProduct(courProdTemp);
sqls.Add(_courProdTempMgr.DeleteSql(courProdTemp));
/*************end**********************************/
ProductSpecTempMgr proSpecTempMgr = new ProductSpecTempMgr("");
ProductSpecTemp proSpecTemp = new ProductSpecTemp { Writer_Id = writerId, product_id = product_Id };
sqls.Add(proSpecTempMgr.TempMoveSpec(proSpecTemp));
sqls.Add(proSpecTempMgr.TempDelete(proSpecTemp));
string priceMaster = priceMasterTempMgr.Move2PriceMaster(priceMasterTemp);
ItemPriceMgr itemPriceMgr = new ItemPriceMgr("");
string itemPrice = itemPriceMgr.SaveFromItem(writerId, product_Id);
product_id = _productDao.TempMove2Pro(movePro, moveCourProd, moveItem, moveCourDetaItem, selItem, priceMaster, itemPrice, sqls);
//把商品推薦屬性臨時表中的數據moveto商品推薦屬性表中,然後刪除商品推薦臨時表 通過product_id指定商品推薦屬性對應的商品
#region 推薦商品屬性插入recommended_product_attribute表中做記錄
if (_rProductAttribute.ExsitInTemp(writerId, int.Parse(product_Id), combo_type) > 0)//判斷臨時表中是否存在 product_Id為傳入的productId
{
DataTable _dt = _rProductAttribute.GetTempList(writerId, int.Parse(product_Id), combo_type);
RecommendedProductAttribute rPA = new RecommendedProductAttribute();
rPA.product_id = Convert.ToUInt32(product_id);
rPA.time_start = 0;
rPA.time_end = 0;
rPA.expend_day = Convert.ToUInt32(_dt.Rows[0]["expend_day"]);
rPA.months = _dt.Rows[0]["months"].ToString();
rPA.combo_type = 1;
if (_rProductAttribute.GetMsgByProductId(product_id) > 0)//如果大於0,表示推薦表中存在數據
{
if (_rProductAttribute.Update(rPA) > 0)
{
_rProductAttribute.DeleteTemp(writerId, int.Parse(product_Id), combo_type);//刪除臨時表中的數據
}
}
else
{
if (_rProductAttribute.Save(rPA) > 0)
{
_rProductAttribute.DeleteTemp(writerId, int.Parse(product_Id), combo_type);//刪除臨時表中的數據
}
}
}
#endregion
if (product_id > 0)
{
piDao.UpdateErpId(product_id.ToString());
}
return product_id;
}
else
{//組合商品
ProductComboTempMgr pcTempMgr = new ProductComboTempMgr("");
ProductComboTemp proComboTemp = new ProductComboTemp { Writer_Id = writerId, Parent_Id = product_Id };
sqls.Add(pcTempMgr.TempMoveCombo(proComboTemp));
sqls.Add(pcTempMgr.TempDelete(proComboTemp));
string selPrice = priceMasterTempMgr.SelectChild(priceMasterTemp);
string priceMaster = priceMasterTempMgr.Move2PriceMasterByMasterId();
ItemPriceTempMgr itemPriceTempMgr = new ItemPriceTempMgr("");
string itemPrice = itemPriceTempMgr.Move2ItemPrice();
sqls.Add(itemPriceTempMgr.Delete(product_Id, combo_type, writerId));
product_id = _productDao.TempMove2Pro(movePro, "", "", "", selPrice, priceMaster, itemPrice, sqls);
//把商品推薦屬性臨時表中的數據moveto商品推薦屬性表中,然後刪除商品推薦臨時表 通過product_id指定商品推薦屬性對應的商品
#region 推薦商品屬性插入recommended_product_attribute表中做記錄
if (_rProductAttribute.ExsitInTemp(writerId, int.Parse(product_Id), combo_type) > 0)//判斷臨時表中是否存在 product_Id為傳入的productId
{
DataTable _dt = _rProductAttribute.GetTempList(writerId, int.Parse(product_Id), combo_type);
RecommendedProductAttribute rPA = new RecommendedProductAttribute();
rPA.product_id = Convert.ToUInt32(product_id);
rPA.time_start = 0;
rPA.time_end = 0;
rPA.expend_day = Convert.ToUInt32(_dt.Rows[0]["expend_day"]);
rPA.months = _dt.Rows[0]["months"].ToString();
rPA.combo_type = 2;
if (_rProductAttribute.GetMsgByProductId(product_id) > 0)//如果大於0,表示推薦表中存在數據
{
if (_rProductAttribute.Update(rPA) > 0)
{
_rProductAttribute.DeleteTemp(writerId, int.Parse(product_Id), combo_type);//刪除臨時表中的數據
}
}
else
{
if (_rProductAttribute.Save(rPA) > 0)
{
_rProductAttribute.DeleteTemp(writerId, int.Parse(product_Id), combo_type);//刪除臨時表中的數據
}
}
}
return product_id;
#endregion
}
}
public List<VendorAccountCustom> VendorAccountDetailExport(VendorAccountDetailQuery query)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlfrom = new StringBuilder();
_iupc = new IupcDao(connStr);
try
{
// sql.AppendFormat(" select p.tax_type, vad.slave_id,vad.vendor_id,vad.order_id,vad.creditcard_1_percent,vad.creditcard_3_percent,vad.sales_limit, ");
sql.AppendFormat(" select vad.slave_id,vad.vendor_id,vad.order_id,vad.sales_limit, ");
sql.AppendFormat(" vad.bonus_percent,vad.freight_low_limit,vad.freight_low_money,vad.freight_normal_limit,vad.freight_normal_money, ");
sql.AppendFormat("vad.freight_return_normal_money,vad.product_money,vad.product_cost,vad.money_creditcard_1,vad.money_creditcard_3,");
sql.AppendFormat("vad.freight_delivery_low,vad.freight_delivery_normal,vad.freight_return_low, ");
//sql.AppendFormat("vad.freight_return_normal,vad.account_amount,FROM_UNIXTIME (vad.account_date,'%Y/%m/%d') account_date,vad.gift,vad.deduction,vad.bag_check_money,vad.freight_return_low_money,imr.free_tax,imr.total_amount,imr.tax_amount, ");
sql.AppendFormat("vad.freight_return_normal,vad.account_amount,account_date,vad.gift,vad.deduction,vad.bag_check_money,vad.freight_return_low_money,imr.free_tax,imr.total_amount,imr.tax_amount, ");
sql.AppendFormat("od.detail_id,od.slave_id,od.item_id,od.item_vendor_id,od.item_mode,od.product_freight_set,od.product_mode,od.product_name,od.product_spec_name,od.single_cost,od.deduct_account,od.parent_id,");
//sql.AppendFormat("od.single_price,od.buy_num,od.event_cost,od.single_money,od.detail_status,FROM_UNIXTIME (os.slave_date_delivery,'%Y/%m/%d') slave_date_delivery,os.slave_date_close,om.order_payment,");
sql.AppendFormat("od.single_price,od.buy_num,od.parent_num,od.event_cost,od.single_money,od.detail_status, slave_date_delivery,os.slave_date_close,om.order_payment,");
sql.AppendFormat(" od.deduct_bonus,od.deduct_welfare,od.deduct_happygo_money, ");
//sql.AppendFormat(" FROM_UNIXTIME( om.order_createdate,'%Y/%m/%d') order_createdate,om.note_admin,od.bag_check_money as od_bag_check_money,tp.parameterName,tp1.parameterName as product_freight,tp2.remark order_status_name ");
sql.AppendFormat(" order_createdate,om.note_admin,od.bag_check_money as od_bag_check_money ");
sqlfrom.AppendFormat(" from order_detail od left join order_slave os on od.slave_id = os.slave_id ");
sqlfrom.AppendFormat("left join vendor_account_detail vad on vad.slave_id = od.slave_id and vad.vendor_id = od.item_vendor_id ");
sqlfrom.AppendFormat("left join order_master om on vad.order_id = om.order_id ");
//sqlfrom.AppendFormat("left join vendor v ON vad.vendor_id = v.vendor_id ");
// sqlfrom.AppendFormat("left join product_item pi ON pi.item_id = od.item_id ");
//sqlfrom.AppendFormat("left join product p ON p.product_id = pi.product_id ");
sqlfrom.AppendFormat("left join invoice_master_record imr on om.order_id=imr.order_id and imr.tax_type=1 and invoice_attribute=1 ");
// sqlfrom.AppendFormat("left join (select * from t_parametersrc where parameterType='payment') tp on om.order_payment = tp.parameterCode ");
//sqlfrom.AppendFormat("left join (select * from t_parametersrc where parameterType='product_freight') tp1 on od.product_freight_set = tp1.parameterCode ");
//sqlfrom.AppendFormat("left join (select * from t_parametersrc where parameterType='order_status') tp2 on od.detail_status = tp2.parameterCode ");
sqlfrom.AppendFormat(" where 1=1 and vad.vendor_id = {0} and vad.account_date >= {1} and vad.account_date <= {2} and od.detail_status <> 89 ", query.vendor_id, query.search_start_time, query.search_end_time);
sqlfrom.AppendFormat(" order by account_date asc, order_id asc,vad.slave_id ASC, od.item_mode asc ");
sql.AppendFormat(sqlfrom.ToString());
//return _access.getDataTable(sql.ToString());
IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr);
List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("payment", "product_freight", "order_status");
List<VendorAccountCustom> list = _access.getDataTableForObj<Model.Custom.VendorAccountCustom>(sql.ToString());
IProductItemImplDao _itemDao = new ProductItemDao(connStr);
IVendorImplDao _vendordao = new VendorDao(connStr);
foreach (VendorAccountCustom q in list)
{
var alist = parameterList.Find(m => m.ParameterType == "payment" && m.ParameterCode == q.Order_Payment.ToString());
var blist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == q.Product_Freight_Set.ToString());
var clist = parameterList.Find(m => m.ParameterType == "order_status" && m.ParameterCode == q.Detail_Status.ToString());
if (alist != null)
{
q.paymentname = alist.parameterName;
}
if (blist != null)
{
q.product_freight = blist.parameterName;
}
if (clist != null)
{
q.order_status_name = clist.remark;
}
Product p = _itemDao.GetTaxByItem(Convert.ToUInt32(q.Item_Id));
DataTable dt = _vendordao.GetVendorDetail(" and vendor_id=" + q.Item_Vendor_Id + " ");
if (p != null)
{
q.tax_type = p.Tax_Type;
}
if (dt.Rows.Count != 0)
{
q.creditcard_1_percent = Convert.ToUInt32(dt.Rows[0]["creditcard_1_percent"].ToString());
// q.creditcard_3_percent = Convert.ToUInt32(dt.Rows[0]["creditcard_3_percent"].ToString());
}
q.accountdate = Common.CommonFunction.GetNetTime(q.account_date);
q.slavedate_delivery = Common.CommonFunction.GetNetTime(q.slave_date_delivery);
q.ordercreatedate = Common.CommonFunction.GetNetTime(q.Order_Createdate);
q.upc_id = _iupc.Getupc(q.Item_Id.ToString(), "1");
}
return list;
}
catch (Exception ex)
{
throw new Exception("VendorAccountMonthDao-->VendorAccountDetailExport-->" + sql.ToString() + ex.Message, ex);
}
}