/// <summary>
/// 取得门店全部库存
/// </summary>
/// <returns></returns>
public static List <ShopStockDto> getStopStockAll()
{
String sql = "SELECT\n" +
" amount,\n"+
" STYLE_SIZE_CD,\n"+
" shop_id,\n"+
" shop_name,\n"+
" MATERIAL_ID,\n"+
" MATERIAL_NAME_CN,\n"+
" MATERIAL_COLOR,\n"+
" SYS_STYLE_ID,\n"+
" SYTLE_YEAR,\n"+
" SYTLE_SEASON,\n"+
" SYS_STYLE_SIZE_CD,\n"+
" STYLE_NAME_CN,\n"+
" STYLE_PUBLISH_CATEGORY_CD \n"+
"FROM\n" +
" v_stock_inventory \n"+
" where amount<>0";
DataTable dataTable = SQLmtm.GetDataTable(sql);
List <ShopStockDto> shopStockDtos = new List <ShopStockDto>();
foreach (DataRow dataRow in dataTable.Rows)
{
shopStockDtos.Add(new ShopStockDto(dataRow));
}
return(shopStockDtos);
}
/// <summary>
/// 根据订单id获取任务
/// </summary>
/// <param name="order_id"></param>
/// <returns></returns>
public static DataTable get订单任务(String order_id)
{
String sql = "SELECT\n" +
" tr.remark_id,\n"+
" tr.order_id,\n"+
" tr.remark,\n"+
" tr.file_name,\n"+
" tr.template_id,\n"+
" tr.`data`,\n"+
" tr.style_id,\n"+
" tr.ref_style_id,\n"+
" tr.CREATE_DATE,\n"+
" tr.parent_id,\n"+
" tr.version,\n"+
" tr.principal,\n"+
" tr.serial_number,\n"+
" tr.status,\n"+
" tr.A_jSON,\n" +
" tr.A_FILE,\n" +
" tt.template_name,\n"+
" ttg.template_group_id,\n"+
" ttg.template_group_name \n"+
"FROM\n" +
" t_remark AS tr\n"+
" LEFT JOIN t_template AS tt ON tr.template_id = tt.template_id\n"+
" LEFT JOIN t_template_group ttg ON tt.template_group_id = ttg.template_group_id \n"+
"WHERE\n" +
" tr.order_id = '"+ order_id + "'";
DataTable dt = SQLmtm.GetDataTable(sql);
return(dt);
}
/// <summary>
/// 查询订单
/// </summary>
/// <param name="orderno"></param>
/// <returns></returns>
public static DataTable GetOrder(String orderno, int order_type)
{
String sql = "SELECT * FROM v_order_with_type WHERE order_type ='" + order_type + "'" +
" and ORDER_NO = '" + orderno + "'";
return(SQLmtm.GetDataTable(sql));
}
/// <summary>
/// 获取选择的尺码的尺寸
/// </summary>
/// <param name="size"></param>
/// <param name="styleid"></param>
/// <returns></returns>
public static DataTable StyleValue(String size, String styleid, DataTable stylesizedt)
{
DataRow st = SQLmtm.GetDataRow("SELECT * FROM s_style_p WHERE SYS_STYLE_ID = '" + styleid + "'");
DataTable dt = new DataTable();
foreach (DataRow dr in stylesizedt.Rows)
{
if (dr["尺寸"].ToString() == size)
{
String sql = "SELECT\n" +
" *,\n"+
" SUBSTRING_INDEX( SIZE_CD, '-',- 1 ) \n"+
"FROM\n" +
" `a_size_fit_p` \n"+
"WHERE\n" +
" FIT_CD = '"+ st["STYLE_FIT_CD"].ToString() + "' \n" +
" AND STYLE_CATEGORY_CD = '"+ st["STYLE_CATEGORY_CD"].ToString() + "' \n" +
" AND SIZEGROUP_CD = '"+ st["STYLE_SIZE_GROUP_CD"] + "' \n" +
" AND SIZE_CD = '"+ dr["SIZE_CD"].ToString() + "';";
dt = SQLmtm.GetDataTable(sql);
break;
}
}
return(dt);
}
private void Frm尺寸修改子页_Load(object sender, EventArgs e)
{
String sql1 = "SELECT\n" +
" t_remark.remark_id,\n" +
" t_remark.order_id,\n" +
" t_remark.remark,\n" +
" t_remark.file_name,\n" +
" t_remark.template_id,\n" +
" t_remark.data jsondata,\n" +
" t_template.template_name,\n" +
" t_template_group.template_group_id,\n" +
" t_template_group.template_group_name \n" +
"FROM\n" +
" t_remark\n" +
" LEFT JOIN t_template ON t_remark.template_id = t_template.template_id\n" +
" LEFT JOIN t_template_group ON t_template.template_group_id = t_template_group.template_group_id";
//this.treeList导航.DataSource = SQLmtm.GetDataTable(sql);
String sql = "SELECT\n" +
" template_id,\n"+
" template_group_id,\n"+
" template_name \n"+
"FROM\n" +
" t_template";
DataTable dt = SQLmtm.GetDataTable(sql);
foreach (DataRow dr in dt.Rows)
{
this.template_choose.Add(dr["template_name"].ToString(), dr["template_id"].ToString());
((DevExpress.XtraEditors.Repository.RepositoryItemComboBox)barEditItem模板.Edit).Items.Add(dr["template_name"].ToString());
}
}
/// <summary>
/// 返回值：K：下线id，V：款式list
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public static Dictionary <String, List <String> > getStyleRelationById(List <String> ids)
{
String sql = "SELECT\n" +
" ROLLOFF_ID,\n"+
" STYLE_ID \n"+
"FROM\n" +
" r_rolloff_style \n"+
"WHERE\n" +
" ROLLOFF_ID IN ( '"+ String.Join("','", ids) + "' )";
Dictionary <String, List <String> > rtn = new Dictionary <String, List <String> >();
DataTable dataTable = SQLmtm.GetDataTable(sql);
foreach (DataRow dataRow in dataTable.Rows)
{
if (rtn.ContainsKey(dataRow["ROLLOFF_ID"].ToString()))
{
// key已经存在
if (!rtn[dataRow["ROLLOFF_ID"].ToString()].Contains(dataRow["STYLE_ID"].ToString()))
{
// 加入新的style
rtn[dataRow["ROLLOFF_ID"].ToString()].Add(dataRow["STYLE_ID"].ToString());
}
}
else
{
// 新增key
rtn.Add(dataRow["ROLLOFF_ID"].ToString(), new List <string>()
{
dataRow["STYLE_ID"].ToString()
});
}
}
return(rtn);
}
/// <summary>
/// 门店验货扫描取barcode方法
/// </summary>
/// <param name="LOG_ID"></param>
/// <returns></returns>
public static BarCodeInfoDto getStockInBarCodeInfo(String LOG_ID)
{
// 读取订单信息
String sql = "select Id,LOG_ID,ORDER_ID,CUSTOMER_ID,SHOP_ID,SHOP_NAME,STYLE_ID,ORDER_DATE,STYLE_NAME_CN,SYTLE_YEAR,SYTLE_SEASON,REF_STYLE_ID,SYTLE_FABRIC_ID,MATERIAL_NAME_CN,MATERIAL_COLOR,STYLE_PUBLISH_CATEGORY_CD,ORDER_NO from a_product_log_p " +
"where Id in (select barcode_id from t_godown_entry) and LOG_ID = '" + LOG_ID + "'";
DataTable dt = SQLmtm.GetDataTable(sql);
if (dt.Rows.Count == 0)
{
sql = "select Id,LOG_ID,ORDER_ID,CUSTOMER_ID,SHOP_ID,SHOP_NAME,STYLE_ID,ORDER_DATE,STYLE_NAME_CN,SYTLE_YEAR,SYTLE_SEASON,REF_STYLE_ID,SYTLE_FABRIC_ID,MATERIAL_NAME_CN,MATERIAL_COLOR,STYLE_PUBLISH_CATEGORY_CD,ORDER_NO from a_product_log_p " +
"where LOG_ID = '" + LOG_ID + "'";
dt = SQLmtm.GetDataTable(sql);
if (dt.Rows.Count == 0)
{
throw new Exception("系统中无本产品标签列印信息");
}
else
{
throw new Exception("本商品未出库");
}
}
else
{
return(new BarCodeInfoDto(dt));
}
}
/// <summary>
/// 获取生产模板
/// </summary>
/// <returns></returns>
public static DataTable Get生产模板()
{
String sql = "SELECT\n" +
" a_dict_p.ITEM_ID,\n"+
" a_dict_p.PARENT_ITEM_CD,\n"+
" a_dict_p.ITEM_CD,\n"+
" a_dict_p.ITEM_VALUE,\n"+
" a_dict_p.ITEM_SORT,\n"+
" a_dict_p.ITEM_REAL_VALUE,\n"+
" a_dict_p.ITEM_NAME_CN,\n"+
" a_dict_p.ITEM_SHORT_NAME_CN,\n"+
" a_dict_p.ITEM_NAME_EN,\n"+
" a_dict_p.ITEM_SHORT_NAME_EN,\n"+
" a_dict_p.ITEM_NAME_JP,\n"+
" a_dict_p.ITEM_SHORT_NAME_JP,\n"+
" a_dict_p.REMARKS,\n"+
" a_dict_p.ENABLE_FLAG,\n"+
" a_dict_p.DELETE_FLAG,\n"+
" a_dict_p.VERSION,\n"+
" a_dict_p.CREATE_DATE,\n"+
" a_dict_p.UPDATE_DATE,\n"+
" a_dict_p.CREATE_USER,\n"+
" a_dict_p.UPDATE_USER \n"+
"FROM\n" +
" a_dict_p \n"+
"WHERE\n" +
" a_dict_p.ITEM_CD = 'PRODUCTION_TEMPLATE'";
return(SQLmtm.GetDataTable(sql));
}
/// <summary>
/// 取得所有任务
/// </summary>
/// <returns></returns>
public static List <UserTaskDTO> getUserTasks()
{
String sql = "SELECT\n" +
" t_remark.remark_id,\n"+
" t_remark.order_id /*订单号*/\n"+
" ,\n"+
" MISSION_TEMPLATE.ITEM_NAME_CN TEMPLATE_NAME,\n"+
" v_order_p.CUSTOMER_ID,\n"+
" v_order_p.CUSTOMER_NAME,\n"+
" v_order_p.SHIPPING_DESTINATION,\n"+
" v_order_p.ORDER_STATUS_CD,\n"+
" v_order_p.ORDER_STATUS_CD_NAME,\n"+
" s_style_p.STYLE_FIT_CD,\n"+
" s_style_p.STYLE_CATEGORY_CD,\n"+
" s_style_p.STYLE_SIZE_CD,\n"+
" s_style_p.STYLE_SIZE_GROUP_CD,\n"+
" t_remark.remark /*备注——文档*/\n"+
" ,\n"+
" t_remark.file_name,\n"+
" t_remark.template_id /*模板id*/\n"+
" ,\n"+
" t_remark.DATA /*json数据*/\n"+
" ,\n"+
" t_remark.style_id,\n"+
" s_style_p.STYLE_NAME_CN,\n"+
" t_remark.ref_style_id,\n"+
" t_remark.CREATE_DATE /*创建时间*/\n"+
" ,\n"+
" t_remark.parent_id /*父ID*/\n"+
" ,\n"+
" t_remark.version /*版本*/\n"+
" ,\n"+
" t_remark.principal /*负责人*/\n"+
" ,\n"+
" CONCAT( a_login_user_p.FIRST_NAME, a_login_user_p.LAST_NAME ) AS USER_NAME /*姓名*/\n"+
" ,\n"+
" t_remark.serial_number /*流水号*/\n"+
" ,\n"+
" t_remark.STATUS,\n"+
" MISSION_STATUS.ITEM_NAME_CN /*状态*/\n"+
" \n"+
"FROM\n" +
" t_remark\n"+
" LEFT JOIN v_order_p ON v_order_p.ORDER_ID = t_remark.order_id\n"+
" LEFT JOIN s_style_p ON s_style_p.SYS_STYLE_ID = t_remark.style_id\n"+
" LEFT JOIN a_login_user_p ON a_login_user_p.LOGIN_USER_ID = t_remark.principal\n"+
" LEFT JOIN ( SELECT item_value, ITEM_NAME_CN FROM a_dict_p WHERE ITEM_CD = 'MISSION_STATUS' ) MISSION_STATUS ON MISSION_STATUS.item_value = t_remark.\n"+
" STATUS LEFT JOIN ( SELECT item_value, ITEM_NAME_CN FROM a_dict_p WHERE ITEM_CD = 'MISSION_TEMPLATE' ) MISSION_TEMPLATE ON MISSION_TEMPLATE.item_value = t_remark.template_id";
DataTable dt = SQLmtm.GetDataTable(sql);
List <UserTaskDTO> userTaskDTOs = new List <UserTaskDTO>();
foreach (DataRow dataRow in dt.Rows)
{
userTaskDTOs.Add(new UserTaskDTO(dataRow));
}
return(userTaskDTOs);
}
private void QueryTag_Load(object sender, EventArgs e)
{
String sql = "SELECT * FROM v_printed_p";
this.searchLookUpEdit1.Properties.DataSource = SQLmtm.GetDataTable(sql);
this.printedView.Dock = DockStyle.Top;
this.dockPanel1.Controls.Add(printedView);
}
/// <summary>
/// 指定门店出入库
/// </summary>
/// <param name="enum进出库类型"></param>
/// <param name="barCode"></param>
/// <param name="shop_id"></param>
private static void generate门店扫码出入库(Enum进出库类型 enum进出库类型, String barCode, String shop_id)
{
String sql = "SELECT dispath_type FROM a_noorder_print_p WHERE clothes_log_id = '" + barCode + "'";
DataTable dataTable = SQLmtm.GetDataTable(sql);
if (dataTable.Rows.Count == 0)
{
throw new Exception("条码" + barCode + "不存在！");
}
else
{
Enum在库类别 enum在库类别;
try
{
enum在库类别 = (Enum在库类别)Convert.ToInt32(dataTable.Rows[0]["dispath_type"].ToString());
}
catch
{
enum在库类别 = Enum在库类别.成品仓;
}
if (enum在库类别.Equals(Enum在库类别.已出库) && enum进出库类型.Equals(Enum进出库类型.出库))
{
throw new Exception("条码" + barCode + "已出库！");
}
if (enum在库类别.Equals(Enum在库类别.在库) && enum进出库类型.Equals(Enum进出库类型.入库))
{
throw new Exception("条码" + barCode + "已入库！");
}
}
// 生成库存明细账
sql = "INSERT INTO t_inventory_sub_ledger ( shop_id, ref_style_id, style_fabric_id, amount, bill_id, STYLE_SIZE_CD, create_date ) SELECT\n" +
shop_id + ",\n" +
"style_id,\n" +
"materials_id,\n";
if (enum进出库类型.Equals(Enum进出库类型.出库))
{
sql += "-1,\n";
}
else
{
sql += "1,\n";
}
sql += "clothes_log_id,\n" +
"size_cd,\n" +
"now( ) \n" +
"FROM\n" +
" a_noorder_print_p \n"+
"WHERE\n" +
" clothes_log_id = '"+ barCode + "'";
SQLmtm.ExecuteSql(sql);
// 更新库存状态
sql = "update a_noorder_print_p set dispath_type = '" + (int)getEnum在库类别(enum进出库类型) + "' WHERE clothes_log_id = '" + barCode + "'";
SQLmtm.ExecuteSql(sql);
}
public static List <款式图片Dto> initStyleInfo(String flag, int page)
{
DataTable dt = SQLmtm.GetDataTable("SELECT\n" +
" style.SYS_STYLE_ID AS styleId,\n" +
" style.STYLE_CD \"styleEntity.styleCd\",\n" +
" style.STYLE_NAME_CN \"styleEntity.styleNameCn\",\n" +
" style.STYLE_CATEGORY_CD \"styleEntity.styleCategoryCd\",\n" +
" style.STYLE_DRESS_CATEGORY \"styleEntity.styleDressCategory\",\n" +
" style.STYLE_DESIGN_TYPE \"styleEntity.styleDesignType\",\n" +
" style.STYLE_PUBLISH_CATEGORY_CD \"styleEntity.stylePublishCategoryCd\",\n" +
" style.SYTLE_YEAR \"styleEntity.sytleYear\",\n" +
" style.SYTLE_SEASON \"styleEntity.sytleSeason\",\n" +
" style.STYLE_FIT_CD \"styleEntity.styleFitCd\",\n" +
" material.MATERIAL_NAME_CN AS \"materialEntity.materialNameCn\",\n" +
" material.MATERIAL_NAME_EN AS \"materialEntity.materialNameEn\",\n" +
" material.material_id \"materialEntity.id\",\n" +
" GROUP_CONCAT( DISTINCT material.MATERIAL_CODE ) \"materialEntity.materialCode\",\n" +
" style.VERSION,\n" +
" style.CREATE_USER,\n" +
" style.STYLE_MAKE_TYPE \"styleEntity.styleMakeType\",\n" +
" style.STYLE_SIZE_GROUP_CD \"styleEntity.styleSizeGroupCd\",\n" +
" style.STYLE_SIZE_CD \"styleEntity.styleSizeCd\",\n" +
" style.STYLE_FIT_BODY_TYPE \"styleEntity.styleFitBodyType\",\n" +
" CONCAT( login_user.FIRST_NAME, login_user.LAST_NAME ) \"user.loginName\",\n" +
" style.CREATE_DATE,\n" +
" style.STYLE_SHOP_TOTAL_PRICE,\n" +
" style.COVER_PHOTO_PATH \"styleEntity.coverPhotoPath\", \n" +
"SUBSTRING_INDEX(style.COVER_PHOTO_PATH,'/',-1) AS picn," +
" CONCAT('https://sshirtmtmbucket.oss-cn-zhangjiakou.aliyuncs.com/sshirtmtm/',SUBSTRING_INDEX(style.COVER_PHOTO_PATH,'/',-1)) AS picurl \n" +
"FROM\n" +
" s_style_p style\n" +
" LEFT JOIN i_material_p material ON FIND_IN_SET( CAST( material.MATERIAL_ID AS CHAR ), style.SYTLE_FABRIC_ID )\n" +
" LEFT JOIN a_login_user_p login_user ON login_user.LOGIN_USER_ID = style.CREATE_USER \n" +
"WHERE\n" +
" style.DELETE_FLAG = 0 \n" +
" and style.ENABLE_FLAG = 1 \n" +
" AND style.STYLE_NAME_CN IS NOT NULL \n" +
" AND style.STYLE_KBN != 'STYLE_SOURCE-STYLE_SOURCE_50' \n" +
" AND ( style.SHOP_ID = 18 OR style.SHOP_ID = 0 )\n" +
"AND style.STYLE_NAME_CN LIKE '%" + flag + "%'\n" +
"GROUP BY\n" +
" style.SYS_STYLE_ID \n" +
"ORDER BY\n" +
" style.UPDATE_DATE DESC \n"
//+" LIMIT " + ((page - 1) * 42).ToString() + ",42"
);
List <款式图片Dto> 款式图片Dtos = new List <款式图片Dto>();
foreach (DataRow dataRow in dt.Rows)
{
款式图片Dtos.Add(new 款式图片Dto(flag, dataRow));
}
return(款式图片Dtos);
}
private void Frm打标_Load(object sender, EventArgs e)
{
this.searchLookUpEdit1.Properties.DataSource = FabricService.GetAllMaterial();
this.searchLookUpEdit款式.Properties.DataSource = StyleService.initStyleInfo("", 1);
this.shop = SQLmtm.GetDataTable("SELECT * FROM t_shop");
foreach (DataRow dr in this.shop.Rows)
{
//this.chicun.Items.Add(Convert.ToString(dr["尺寸"]));
this.shopname.Items.Add(Convert.ToString(dr["shop_name"]));
}
}
/// <summary>
/// 面料log信息
/// </summary>
/// <returns></returns>
public static DataTable GetMianLiaoLogDT()
{
String sql = "SELECT\n" +
" * \n"+
"FROM\n" +
" t_material_log AS ml\n"+
" LEFT JOIN i_material_p AS ip ON ml.material_id = ip.MATERIAL_ID WHERE ml.enable_flag='1';";
DataTable dt = SQLmtm.GetDataTable(sql);
return(dt);
}
/// <summary>
/// 去设计点信息
/// </summary>
/// <param name="itemcd">设计点</param>
/// <param name="str">模糊查询字段</param>
/// <returns></returns>
public static List <设计点图片Dto> GetDesign(String itemcd, String str)
{
String sql = "SELECT\n" +
" a.DESIGN_ID id,\n" +
" a.STYLE_CATEGORY_CD styleCategoryCD,\n" +
" a.FILE_ID fileID,\n" +
" a.ITEM_CD itemCD,\n" +
" a.ITEM_VALUE itemValue,\n" +
" a.ITEM_NAME_CN itemNameCN,\n" +
" file.UPLOAD_FILE \"picn\",\n" +
" CONCAT( 'shirtmtm.com',file.FILE_PATH, file.UPLOAD_FILE ) \"picurl\",\n" +
" a.ITEM_NAME_EN itemNameEN,\n" +
" a.ITEM_NAME_JP itemNameJP,\n" +
" IFNULL( a.ITEM_COST, 0 ) itemCost,\n" +
" a.REMARKS remarks,\n" +
" a.ENABLE_FLAG enableFlag,\n" +
" a.DELETE_FLAG deleteFlag,\n" +
" a.HAVETO_FLAG haveToFlag,\n" +
" a.VERSION version,\n" +
" a.CREATE_DATE createDate,\n" +
" a.CREATE_USER \"createBy.id\",\n" +
" a.UPDATE_USER \"updateBy.id\",\n" +
" a.ITEM_SORT itemSort,\n" +
" a.ITEM_CATEGORY_CD itemCategoryCD,\n" +
" file.FILE_ID \"uploadFile.fileId\",\n" +
" file.FILE_SOURCE \"uploadFile.fileSource\",\n" +
" file.MODULE_KBN \"uploadFile.moduleKbn\",\n" +
" file.FTP_FILE \"uploadFile.ftpFile\",\n" +
" file.FILE_PATH \"uploadFile.filePath\",\n" +
" CONCAT( p.FIRST_NAME, p.LAST_NAME ) \"updateBy.firstName\" \n" +
"FROM\n" +
" a_designoption_p a\n" +
" LEFT JOIN a_login_user_p p ON a.UPDATE_USER = p.login_user_id\n" +
" LEFT JOIN w_upload_file_p file ON a.FILE_ID = file.FILE_ID \n" +
" LEFT JOIN a_ognization_desgin_r adr ON a.DESIGN_ID = adr.DESGIN_ID \n" +
"WHERE\n" +
" a.STYLE_CATEGORY_CD = 'STYLE_CATEGORY-SHIRT' and a.DESIGN_ID IN ( SELECT DESGIN_ID FROM a_shop_desgin_r WHERE SHOP_ID = 18 ) \n" +
" AND a.ITEM_CD='" + itemcd + "'\n" +
" AND a.DELETE_FLAG = 0 \n" +
" AND adr.OGNIZATION_ID = 95 \n" +
" AND (a.ITEM_VALUE LIKE '%" + str + "%' OR a.ITEM_NAME_CN LIKE '%" + str + "%') " +
"ORDER BY\n" +
" a.item_sort,\n" +
" a.UPDATE_DATE DESC";
DataTable dt = SQLmtm.GetDataTable(sql);
List <设计点图片Dto> 设计点图片dtos = new List <设计点图片Dto>();
foreach (DataRow dr in dt.Rows)
{
设计点图片dtos.Add(new 设计点图片Dto(dr));
}
return(设计点图片dtos);
}
/// <summary>
/// 查询所有模板
/// </summary>
/// <returns></returns>
public static DataTable GetTemplate()
{
String sql = "SELECT\n" +
" ITEM_CD,\n"+
" ITEM_VALUE AS template_id,\n"+
" ITEM_NAME_CN AS template_name \n"+
"FROM\n" +
" a_dict_p \n"+
"WHERE\n" +
" ITEM_CD = 'MISSION_TEMPLATE'";
return(SQLmtm.GetDataTable(sql));
}
private static List <尺寸呈现dto> GetThisSize(String STYLE_FIT_CD, String STYLE_CATEGORY_CD, String STYLE_SIZE_CD, String STYLE_SIZE_GROUP_CD, String Style_Id, Boolean isRef)
{
List <尺寸呈现dto> 尺寸呈现 = new List <尺寸呈现dto>();
String sql;
if (!isRef) // 是否标准款
{
sql = "SELECT\n" +
" S1.*,\n" +
" S2.* \n" +
"FROM\n" +
" ( SELECT * FROM a_size_fit_p WHERE FIT_CD = 'FIT_BODY_TYPE-1914F' AND STYLE_CATEGORY_CD = 'STYLE_CATEGORY-SHIRT' AND SIZE_CD = 'EGS_GROUP_SIZE-42' AND SIZEGROUP_CD = 'GROUP_SIZE-EGS_GROUP_SIZE' ) AS s1\n" +
" LEFT JOIN ( SELECT * FROM a_reasonable_p WHERE styleID IN ( SELECT REF_STYLE_ID FROM s_style_p WHERE SYS_STYLE_ID = " + Style_Id + " ) ) AS s2 ON s1.ITEM_VALUE = s2.itemValue;";
}
else
{
sql = "SELECT\n" +
" S1.*,\n" +
" S2.* \n" +
"FROM\n" +
" ( SELECT * FROM a_size_fit_p WHERE" +
" FIT_CD = '" + STYLE_FIT_CD + "'" +
" AND STYLE_CATEGORY_CD = '" + STYLE_CATEGORY_CD + "'" +
" AND SIZE_CD = '" + STYLE_SIZE_CD + "'" +
" AND SIZEGROUP_CD = '" + STYLE_SIZE_GROUP_CD + "' ) AS s1\n" +
" LEFT JOIN ( SELECT * FROM a_reasonable_p WHERE styleID = " + Style_Id + " ) AS s2 ON s1.ITEM_VALUE = s2.itemValue;";
}
DataTable dt = SQLmtm.GetDataTable(sql);
foreach (DataRow dr in dt.Rows)
{
尺寸呈现.Add(
new 尺寸呈现dto(
dr["ITEM_CD"].ToString()
, dr["ITEM_VALUE"].ToString()
, ""
, ""
, Convert.ToDouble(dr["ITEM_FIT_VALUE"].ToString())
, 0
, 0
, dr["ITEM_NAME_CN"].ToString()
, Convert.ToDouble(dr["leastReasonable"].ToString() == "" ? "0" : dr["leastReasonable"].ToString())
, Convert.ToDouble(dr["maxReasonable"].ToString() == "" ? "0" : dr["maxReasonable"].ToString())
)
);
}
return(尺寸呈现);
}
/// <summary>
/// 根据条码取得入库信息
/// </summary>
/// <param name="LOG_ID"></param>
/// <returns></returns>
public static DataTable getStockInInfo(String LOG_ID)
{
String sql = "SELECT"
+ " t_godown_bill.godown_code,"
+ " t_shop.shop_id,"
+ " t_shop.shop_name,"
+ " t_godown_bill.godown_date"
+ " FROM"
+ " t_godown_bill "
+ " left join t_shop on t_godown_bill.shop_id = t_shop.shop_id"
+ " WHERE"
+ " godown_id IN(SELECT godown_id FROM t_godown_entry WHERE barcode_id IN (SELECT id FROM a_product_log_p WHERE LOG_ID = '" + LOG_ID + "') ); ";
return(SQLmtm.GetDataTable(sql));
}
/// <summary>
/// 查询未打印订单
/// </summary>
/// <param name="orderno"></param>
/// <returns></returns>
public static DataTable GetNotPrintedData(String orderno, DateTime startTime, DateTime endTime, int order_type)
{
String sql = "SELECT " +
" * "+
"FROM " +
" v_order_with_type "+
"WHERE " +
" ORDER_NO NOT IN ( SELECT ORDER_NO FROM a_product_log_p ) "+
" AND ORDER_NO LIKE '%"+ orderno + "%' " +
" and ORDER_DATE between '" + startTime.ToString() + "' and '" + endTime.ToString() + "'" +
" and order_type ='" + order_type + "'" +
" order by ORDER_DATE";
return(SQLmtm.GetDataTable(sql));
}
/// <summary>
/// 取得所有任务模板
/// </summary>
/// <returns></returns>
public static List <TaskTemplateDTO> getTaskTemplateDTO()
{
String sql = "SELECT\n" +
" TEMPLATE_ID,TEMPLATE_NAME \n"+
"FROM\n" +
" t_task_template;";
DataTable dt = SQLmtm.GetDataTable(sql);
List <TaskTemplateDTO> taskTemplateDTOs = new List <TaskTemplateDTO>();
foreach (DataRow dataRow in dt.Rows)
{
taskTemplateDTOs.Add(new TaskTemplateDTO(dataRow));
}
return(taskTemplateDTOs);
}
public static List <面料DTO> get面料DTOs(List <String> SYTLE_FABRIC_ID)
{
String sql = "SELECT\n" +
" id,\n"+
" fileId,\n"+
" materialNameCn,\n"+
" materialNameEn,\n"+
" materialCode,\n"+
" materialUseType,\n"+
" materialTypeCd,\n"+
" materialComposition,\n"+
" materialSpec,\n"+
" materialUnitCd,\n"+
" materialYear,\n"+
" materialColor,\n"+
" materialSeason,\n"+
" remarks,\n"+
" materialStyleCategory,\n"+
" modelFilepath,\n"+
" filePath,\n"+
" createby_lastName,\n"+
" createby_firstName,\n"+
" materialPrice_materialSalePrice,\n"+
" inventoryMaterial_materialQuantity,\n"+
" materialLevel,\n"+
" materialStyle,\n"+
" materialWeave,\n"+
" materiaFacPrice,\n"+
" mianliao,\n"+
" picn,\n"+
" picurl \n"+
"FROM\n" +
" V_MATERIAL_CATEGORY_Fabric \n"+
"WHERE\n" +
" id IN ( '"+ String.Join("','", SYTLE_FABRIC_ID) + "' );";
DataTable dt = SQLmtm.GetDataTable(sql);
List <面料DTO> 面料DTOs = new List <面料DTO>();
foreach (DataRow dr in dt.Rows)
{
面料DTOs.Add(new 面料DTO(dr));
}
return(面料DTOs);
}
/// <summary>
/// 取得所有已下线DTO
/// </summary>
/// <returns></returns>
public static List <线DTO> getAll下线DTO()
{
String sql = "SELECT\n" +
" id,\n"+
"code \n" +
"FROM\n" +
" t_rolloff \n"+
"WHERE\n" +
" flag = 0";
List <线DTO> 线DTOs = new List <线DTO>();
DataTable dataTable = SQLmtm.GetDataTable(sql);
foreach (DataRow dataRow in dataTable.Rows)
{
线DTOs.Add(new 线DTO(dataRow));
}
return(线DTOs);
}
private void Frm更改尺寸测_Load(object sender, EventArgs e)
{
String sql = "SELECT\n" +
" * \n"+
"FROM\n" +
" a_size_fit_p \n"+
"WHERE\n" +
" FIT_CD = 'FIT_BODY_TYPE-1914F' \n"+
" AND STYLE_CATEGORY_CD = 'STYLE_CATEGORY-SHIRT' \n"+
" AND SIZEGROUP_CD = 'GROUP_SIZE-EGS_GROUP_SIZE' "+
" AND SIZE_CD = 'EGS_GROUP_SIZE-38';";
DataTable dt = SQLmtm.GetDataTable(sql);
foreach (DataRow dr in dt.Rows)
{
this.尺寸dtos.Add(new 当前尺寸dto(dr["ITEM_CD"].ToString(), dr["ITEM_VALUE"].ToString(), "", "", Convert.ToDouble(dr["ITEM_FIT_VALUE"].ToString()), 0, 0, dr["ITEM_NAME_CN"].ToString()));
}
this.gridControl1.DataSource = this.尺寸dtos;
}
/// <summary>
/// 获取客户基本信息
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static List <CustomerInformation> GetCustomerInformation(int id)
{
List <CustomerInformation> ci = new List <CustomerInformation>();
String sql = "SELECT " +
"fcr.id, " +
"fcr.CUSTOMER_ID, " +
"fcr.CUSTOMER_NAME, " +
"fcr.SEX, " +
"fcr.AGE, " +
"fr.STYLE_CATEGORY_CD, " +
"fr.ITEM_CD, " +
"fr.ITEM_VALUE, " +
"fr.FIT_VALUE, " +
"fpp.PROPERTY_NAME_CN, " +
"dop.ITEM_NAME_CN " +
"FROM " +
" a_customer_fit_count_r AS fcr "+
" LEFT JOIN a_customer_fit_r fr ON fcr.id = fr.FIT_COUNT_ID "+
" LEFT JOIN a_fit_property_p AS fpp ON fr.ITEM_VALUE = fpp.PROPERTY_VALUE "+
" LEFT JOIN w_dict_option_p AS dop ON fr.ITEM_VALUE = dop.ITEM_CD "+
" AND fr.FIT_VALUE = dop.ITEM_VALUE "+
"WHERE " +
" fcr.CUSTOMER_ID = '"+ id.ToString() + "' " +
" AND fcr.DEFAULT_FLAG = '1'";
DataTable dt = SQLmtm.GetDataTable(sql);
if (dt.Rows.Count != 0)
{
ci.Add(new CustomerInformation("收件人姓名", dt.Rows[0]["CUSTOMER_NAME"].ToString()));
foreach (DataRow dr in dt.Rows)
{
if (dr["ITEM_NAME_CN"].ToString() != "")
{
ci.Add(new CustomerInformation(dr["PROPERTY_NAME_CN"].ToString(), dr["ITEM_NAME_CN"].ToString()));
}
else
{
ci.Add(new CustomerInformation(dr["PROPERTY_NAME_CN"].ToString(), dr["FIT_VALUE"].ToString()));
}
}
}
return(ci);
}
/// <summary>
/// 取得所有用户
/// </summary>
/// <returns></returns>
public static List <UserDTO> getUserAll()
{
String sql = "SELECT\n" +
" LOGIN_USER_ID,\n"+
" FIRST_NAME,\n"+
" LAST_NAME,\n"+
" LOGIN_NAME,\n"+
" PASSWORD,\n"+
" SEX,\n"+
" BIRTH_DATE,\n"+
" COUNTRY,\n"+
" CITY,\n"+
" ADDRESS,\n"+
" TEL_NO,\n"+
" MOBILE,\n"+
" EMAIL,\n"+
" FAX_NO,\n"+
" USER_TYPE,\n"+
" EMAIL_RANDOM_CODE,\n"+
" EMAIL_SEND_TIME,\n"+
" MAILSEND_FLAG,\n"+
" ADMIN_FLAG,\n"+
" DELETE_FLAG,\n"+
" LOGIN_IP,\n"+
" LAST_DATE,\n"+
" REMARKS,\n"+
" VERSION,\n"+
" CREATE_DATE,\n"+
" UPDATE_DATE,\n"+
" CREATE_USER,\n"+
" UPDATE_USER \n"+
"FROM\n" +
" a_login_user_p";
DataTable dt = SQLmtm.GetDataTable(sql);
List <UserDTO> UserDTOs = new List <UserDTO>();
foreach (DataRow dr in dt.Rows)
{
UserDTOs.Add(new UserDTO(dr));
}
return(UserDTOs);
}
public static List <Dto设计点> get设计点BySYS_STYLE_ID(String SYS_STYLE_ID)
{
String sql = "SELECT\n" +
" s_style_option_r.SYTLE_OPTION_ID,\n"+
" s_style_option_r.SYS_STYLE_ID,\n"+
" s_style_option_r.ITEM_CD,\n"+
" s_style_option_r.ITEM_VALUE,\n"+
" s_style_option_r.OPTION_VALUE,\n"+
" s_style_option_r.ITEM_TYPE_NAME_CN,\n"+
" s_style_option_r.ITEM_NAME_CN,\n"+
" w_upload_file_p.UPLOAD_FILE \n"+
"FROM\n" +
" (\n"+
" SELECT\n"+
" sr.SYTLE_OPTION_ID,\n"+
" sr.SYS_STYLE_ID,\n"+
" sr.ITEM_CD,\n"+
" sr.ITEM_VALUE,\n"+
" sr.OPTION_VALUE,\n"+
" ap1.ITEM_NAME_CN AS ITEM_TYPE_NAME_CN,\n"+
" ap.ITEM_NAME_CN,\n"+
" ap.FILE_ID \n"+
" FROM\n"+
" s_style_option_r AS sr\n"+
" LEFT JOIN a_designoption_p AS ap1 ON sr.ITEM_VALUE = ap1.ITEM_VALUE\n"+
" LEFT JOIN a_designoption_p AS ap ON sr.OPTION_VALUE = ap.ITEM_VALUE \n"+
" AND sr.ITEM_VALUE = ap.ITEM_CD \n"+
" WHERE\n"+
" sr.SYS_STYLE_ID = '"+ SYS_STYLE_ID + "' \n" +
" AND ap.ITEM_NAME_CN IS NOT NULL \n"+
" ) AS s_style_option_r\n"+
" LEFT JOIN w_upload_file_p ON w_upload_file_p.FILE_ID = s_style_option_r.FILE_ID";
List <Dto设计点> 设计点s = new List <Dto设计点>();
DataTable dataTable = SQLmtm.GetDataTable(sql);
foreach (DataRow dataRow in dataTable.Rows)
{
设计点s.Add(new Dto设计点(dataRow));
}
return(设计点s);
}
public static 款式图片一览Dto getStyleByORDER_ID(String ORDER_ID)
{
String sql = "SELECT\n" +
" SYS_STYLE_ID,\n"+
" CUSTOMER_COUNT_ID,\n"+
" STYLE_CD,\n"+
" STYLE_CATEGORY_CD,\n"+
" STYLE_DRESS_CATEGORY,\n"+
" STYLE_DESIGN_TYPE,\n"+
" STYLE_PUBLISH_CATEGORY_CD,\n"+
" REF_STYLE_ID,\n"+
" STYLE_NAME_CN,\n"+
" STYLE_NAME_EN,\n"+
" STYLE_FIT_CD,\n"+
" SYTLE_YEAR,\n"+
" SYTLE_SEASON,\n"+
" SYTLE_FABRIC_ID,\n"+
" STYLE_SIZE_GROUP_CD,\n"+
" STYLE_SIZE_CD,\n"+
" STYLE_MAKE_TYPE,\n"+
" STYLE_MATERIAL_NUMBER,\n"+
" STYLE_DESIGN_PRICE,\n"+
" CONCAT( 'https://sshirtmtmbucket.oss-cn-zhangjiakou.aliyuncs.com/sshirtmtm/', SUBSTRING_INDEX( COVER_PHOTO_PATH, '/',- 1 ) ) AS PIC_URL,\n"+
" SUBSTRING_INDEX( COVER_PHOTO_PATH, '/',- 1 ) AS PIC_NAME,\n"+
" ENABLE_FLAG,\n"+
" CREATE_DATE\n"+
"FROM\n" +
" s_style_p\n"+
" where s_style_p.SYS_STYLE_ID in (select STYLE_ID from o_order_p where order_id = '" + ORDER_ID + "')" +
" order by CREATE_DATE";
List <款式图片一览Dto> 款式图片一览Dtos = new List <款式图片一览Dto>();
DataTable dataTable = SQLmtm.GetDataTable(sql);
foreach (DataRow dataRow in dataTable.Rows)
{
return(new 款式图片一览Dto(dataRow));
}
throw new Exception("没有款式信息");
}
/// <summary>
/// 取得所有款式信息
/// </summary>
/// <returns></returns>
public static List <款式图片一览Dto> getStyleByIds(List <String> ids)
{
String sql = "SELECT\n" +
" SYS_STYLE_ID,\n"+
" CUSTOMER_COUNT_ID,\n"+
" STYLE_CD,\n"+
" STYLE_CATEGORY_CD,\n"+
" STYLE_DRESS_CATEGORY,\n"+
" STYLE_DESIGN_TYPE,\n"+
" STYLE_PUBLISH_CATEGORY_CD,\n"+
" REF_STYLE_ID,\n"+
" STYLE_NAME_CN,\n"+
" STYLE_NAME_EN,\n"+
" STYLE_FIT_CD,\n"+
" SYTLE_YEAR,\n"+
" SYTLE_SEASON,\n"+
" SYTLE_FABRIC_ID,\n"+
" STYLE_SIZE_GROUP_CD,\n"+
" STYLE_SIZE_CD,\n"+
" STYLE_MAKE_TYPE,\n"+
" STYLE_MATERIAL_NUMBER,\n"+
" STYLE_DESIGN_PRICE,\n"+
" CONCAT( 'https://sshirtmtmbucket.oss-cn-zhangjiakou.aliyuncs.com/sshirtmtm/', SUBSTRING_INDEX( COVER_PHOTO_PATH, '/',- 1 ) ) AS PIC_URL,\n"+
" SUBSTRING_INDEX( COVER_PHOTO_PATH, '/',- 1 ) AS PIC_NAME,\n"+
" ENABLE_FLAG,\n"+
" CREATE_DATE\n"+
"FROM\n" +
" v_style_p\n"+
" where v_style_p.SYS_STYLE_ID in ('" + String.Join("','", ids) + "')" +
" order by CREATE_DATE";
List <款式图片一览Dto> 款式图片一览Dtos = new List <款式图片一览Dto>();
DataTable dataTable = SQLmtm.GetDataTable(sql);
foreach (DataRow dataRow in dataTable.Rows)
{
款式图片一览Dto 款式图片一览Dto = new 款式图片一览Dto(dataRow);
款式图片一览Dtos.Add(款式图片一览Dto);
}
return(款式图片一览Dtos);
}
/// <summary>
/// build设计点
/// </summary>
/// <param name="styleid"></param>
/// <returns></returns>
public static List <设计点DTO> Get设计点DTOs(String styleid)
{
String sql = "SELECT\n" +
" sr.*,\n"+
" adp.ITEM_NAME_CN AS OPTION_NAME,\n"+
" adp1.ITEM_NAME_CN AS ITEM_NAME \n"+
"FROM\n" +
" s_style_option_r AS sr\n"+
" LEFT JOIN a_designoption_p AS adp ON sr.OPTION_VALUE = adp.ITEM_VALUE\n"+
" LEFT JOIN a_designoption_p AS adp1 ON sr.ITEM_VALUE = adp1.ITEM_VALUE \n"+
"WHERE\n" +
" sr.SYS_STYLE_ID = '"+ styleid + "';";
DataTable dt = SQLmtm.GetDataTable(sql);
List <设计点DTO> 设计点dtos = new List <设计点DTO>();
foreach (DataRow dr in dt.Rows)
{
设计点dtos.Add(new 设计点DTO(dr));
}
return(设计点dtos);
}
/// <summary>
/// 查询用户
/// </summary>
/// <param name="str"></param>
/// <returns></returns>
public static DataTable GetCustomerData(String str)
{
String sql = "SELECT " +
" acp.CUSTOMER_ID AS ID, "+
" CONCAT( CONCAT( acp.CUSTOMER_FIRST_NAME, ' ' ), acp.CUSTOMER_LAST_NAME ) AS 微信名称, "+
" acp.MOBILE AS 手机, "+
" acp.CREATE_DATE AS 注册时间, "+
" cap.CONSIGNEE AS 客户姓名 "+
"FROM " +
" a_customer_p AS acp "+
"LEFT JOIN a_customer_address_p AS cap ON cap.CUSTOMER_ID = acp.CUSTOMER_ID " +
"WHERE " +
" acp.CUSTOMER_FIRST_NAME LIKE '%"+ str + "%' " +
" OR acp.CUSTOMER_LAST_NAME LIKE '%"+ str + "%' " +
" OR acp.MOBILE LIKE '%"+ str + "%' " +
" OR cap.CONSIGNEE LIKE '%"+ str + "%' " +
"ORDER BY " +
" acp.CREATE_DATE DESC ";
return(SQLmtm.GetDataTable(sql));
}