/// <summary> /// 获得数据列表 /// </summary> public DataSet GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select id,date_time,customer_id,user_id,change_total,change_type,change_desc,admin_id,erp_dakuanhao,erp_time,status,debit_account_code,credit_account_code,adjust_sn "); strSql.Append(" FROM fms_kuan_adjust "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 获得数据列表 /// </summary> public DataSet GetLists(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select max(period_start_date) as period_start_date "); strSql.Append(" FROM fms_ac_period "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 获得数据列表 /// </summary> public DataSet GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select id,refund_id,refund_sn,emp_id,emp_code,emp_name,log_info,log_time "); strSql.Append(" FROM fms_refund_log "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 获得数据列表 /// </summary> public DataSet GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select id,dep_id,code_str,creater,create_time "); strSql.Append(" FROM fms_costitem_dep "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 获得数据列表 /// </summary> public DataSet GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select id, datetime,price,account,user_id,user_name,channel,deal,stats,notes,is_delete,erp_dakuanhao,add_time,erp_bank_id,confirm_admin_id,CAST( confirm_time as char) as confirm_time,CAST( erp_time as char) as erp_time,erp_type,acc_type,is_send_out "); strSql.Append(" FROM fms_dkmassage "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 获得数据列表 /// </summary> public DataSet GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select Id,factoryId,suppliers_id,suppliers_code,suppliers_name,supply_bank,supply_bankCode,pay_count,create_Id,create_code,create_name,create_date,Is_stop "); strSql.Append(" FROM fms_supply_bank "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 获得数据列表 /// </summary> public DataSet GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select id,jzdate,jytime,jiefang,daifang,name,number,user_qq,admin_name,admin_id,erp_dakuanhao,status,file_time,confirm_time,dkmassage_id,user_id,acc_type "); strSql.Append(" FROM fms_kuan "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 获得数据列表 /// </summary> public DataSet GetLists(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select r.*,e.is_cash_bank "); strSql.Append(" FROM fms_refund_bill r left join fms_bank_list_excel e on r.pay_method = e.collection_bank_code "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 获得数据列表 /// </summary> public DataSet GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select refund_id,refund_sn,refund_type,refund_status,emp_id,emp_code,emp_name,apply_date,customer_id,customer_name,refund_money,refund_capital_money,refund_remark,refund_bank_name,refund_bank_account,pay_method,pay_id,pay_code,pay_name,pay_date,approvalOne_id,approvalOne_code,approvalOne_name,approvalOne_date,approvalTwo_id,approvalTwo_code,approvalTwo_name,approvalTwo_date,approvalThree_id,approvalThree_code,approvalThree_name,approvalThree_date,approvalFour_id,approvalFour_code,approvalFour_name,approvalFour_date,is_del,is_send_out,kingdee_in_code,kingdee_out_code,user_id,pay_method_name,print_num,kingdee_current_account "); strSql.Append(" FROM fms_refund_bill "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 获得数据列表 /// </summary> public DataSet GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select Id,Factory_Id,C_customerid,C_customer_code,C_customername,C_userid,C_username,period_year,period_start,period_end,period_start_date,period_end_date,Is_stop,C_type "); strSql.Append(" FROM fms_ac_period "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 获得数据列表 /// </summary> public DataSet GetMaxList(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select max(code) as code "); strSql.Append(" FROM fms_costitem "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 获得数据列表 /// </summary> public DataSet GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select id,Factory_Id,code,title,item_order,creater,createtime,parent_code "); strSql.Append(" FROM fms_costitem "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 获得数据列表 /// </summary> public DataSet GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append("select id,collection_bank_id,collection_bank_code,collection_bank_name,collection_account,dk_time,dk_time_only,dk_money,dk_account,dk_username,dk_bank_name,dk_channel,dk_message_remarks,start_line,excel_exist_collection_account,sort,excel_type,is_use,is_show_full,acc_type,is_collect,is_send_out,is_cash_bank "); strSql.Append(" FROM fms_bank_list_excel "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } strSql.Append(" order by sort "); return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 获得数据列表 /// </summary> public DataSet GetListBank(string strWhere) { StringBuilder strSql = new StringBuilder(); strSql.Append(" select e.is_cash_bank,e.collection_bank_code,e.collection_bank_name,e.is_send_out,d.id, d.datetime,d.price,d.account,d.user_id,d.user_name,d.channel,d.deal,stats,d.notes,d.is_delete,d.erp_dakuanhao,d.is_inner_Journal, "); strSql.Append(" d.add_time,d.erp_bank_id,d.confirm_admin_id,CAST( d.confirm_time as char) as confirm_time,CAST( d.erp_time as char) as erp_time,d.erp_type,d.acc_type,d.kingdee_current_account "); strSql.Append(" FROM fms_dkmassage d inner join fms_bank_list_excel e on d.erp_bank_id=e.collection_bank_id "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 加载客户的调账信息 /// </summary> public DataSet GetList(int PageSize, int PageIndex, int mark, string strWhere, DataTable dt, string filedOrder, out string Total) { StringBuilder strSql = new StringBuilder(); StringBuilder strSql1 = new StringBuilder(); if (mark == 1) { strSql.Append(" select distinct * from ( "); strSql1.Append(" select count(distinct id) from ( "); if (dt != null && dt.Rows.Count > 0) { for (int i = 0; i < dt.Rows.Count; i++) { if (i == 0) { strSql.Append(" select id,date_time,customer_id,user_id,change_total,change_type,change_desc,admin_id,erp_dakuanhao,erp_time,status,debit_account_code,credit_account_code,adjust_sn FROM fms_kuan_adjust "); strSql.Append(" where user_id ='" + dt.Rows[i]["userId"].ToString() + "' and date_time>='" + dt.Rows[i]["datetime"].ToString() + "' "); strSql1.Append(" select id FROM fms_kuan_adjust "); strSql1.Append(" where user_id ='" + dt.Rows[i]["userId"].ToString() + "' and date_time>='" + dt.Rows[i]["datetime"].ToString() + "' "); } else { strSql.Append(" union all select id,date_time,customer_id,user_id,change_total,change_type,change_desc,admin_id,erp_dakuanhao,erp_time,status,debit_account_code,credit_account_code,adjust_sn FROM fms_kuan_adjust "); strSql.Append(" where user_id ='" + dt.Rows[i]["userId"].ToString() + "' and date_time>='" + dt.Rows[i]["datetime"].ToString() + "' "); strSql1.Append(" union all select id FROM fms_kuan_adjust "); strSql1.Append(" where user_id ='" + dt.Rows[i]["userId"].ToString() + "' and date_time>='" + dt.Rows[i]["datetime"].ToString() + "' "); } } } strSql.Append(" ) as g "); strSql1.Append(" ) as g "); } else if (mark == 2) { strSql.Append("select id,date_time,customer_id,user_id,change_total,change_type,change_desc,admin_id,erp_dakuanhao,erp_time,status,debit_account_code,credit_account_code,adjust_sn "); strSql.Append(" FROM fms_kuan_adjust "); strSql1.Append(" select count(id) FROM fms_kuan_adjust "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); strSql1.Append(" where " + strWhere); } } strSql.Append(" order by " + filedOrder + " limit " + (PageIndex - 1) * PageSize + "," + PageSize); Total = FMS_DbHelperMySQL.Query(strSql1.ToString()).Tables[0].Rows[0][0].ToString(); return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 加载公司户认款信息 /// </summary> public DataSet GetList(int PageSize, int PageIndex, string strWhere, string filedOrder, out string Total) { StringBuilder strSql = new StringBuilder(); StringBuilder strSql1 = new StringBuilder(); strSql.Append("select id,jzdate,jytime,jiefang,daifang,name,number,user_qq,admin_name,admin_id,erp_dakuanhao,status,file_time,confirm_time,dkmassage_id,user_id,acc_type "); strSql.Append(" FROM fms_kuan "); strSql1.Append(" select count(id) FROM fms_kuan "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); strSql1.Append(" where " + strWhere); } strSql.Append(" order by " + filedOrder + " limit " + (PageIndex - 1) * PageSize + "," + PageSize); Total = FMS_DbHelperMySQL.Query(strSql1.ToString()).Tables[0].Rows[0][0].ToString(); return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 加载打开银行excel格式信息 /// </summary> public DataSet GetList(int PageSize, int PageIndex, string strWhere, string filedOrder, out string Total) { StringBuilder strSql = new StringBuilder(); StringBuilder strSql1 = new StringBuilder(); strSql.Append("select id,collection_bank_id,collection_bank_code,collection_bank_name,collection_account,dk_time,dk_time_only,dk_money,dk_account,dk_username,dk_bank_name,dk_channel,dk_message_remarks,start_line,excel_exist_collection_account,sort,excel_type,is_use,is_show_full,acc_type,is_collect,is_send_out,is_cash_bank"); strSql.Append(" FROM fms_bank_list_excel "); strSql1.Append(" select count(id) FROM fms_bank_list_excel "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); strSql1.Append(" where " + strWhere); } strSql.Append(" order by " + filedOrder + " limit " + (PageIndex - 1) * PageSize + "," + PageSize); Total = FMS_DbHelperMySQL.Query(strSql1.ToString()).Tables[0].Rows[0][0].ToString(); return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 加载公司户/个人户待认款信息 /// </summary> public DataSet GetList(int PageSize, int PageIndex, string strWhere, string filedOrder, out string Total) { StringBuilder strSql = new StringBuilder(); StringBuilder strSql1 = new StringBuilder(); strSql.Append(" select m.id,m.datetime,m.price,m.account,b.collection_bank_name,m.user_id,m.user_name,m.channel,m.deal,m.stats,m.notes,m.is_delete,m.erp_dakuanhao,m.add_time,m.erp_bank_id,m.is_inner_Journal,m.is_bill_handle, "); strSql.Append(" m.confirm_admin_id,CAST( m.confirm_time as char) as confirm_time,CAST( m.erp_time as char) as erp_time,m.erp_type,m.acc_type,k.status,m.is_send_out,m.kingdee_out_code,m.from_type "); strSql.Append(" FROM fms_dkmassage m inner join fms_bank_list_excel b on m.erp_bank_id=b.collection_bank_id "); strSql.Append(" left join (select dkmassage_id,status from fms_kuan where status='0' ) as k on m.id=k.dkmassage_id "); strSql1.Append(" select count(m.id) FROM fms_dkmassage m inner join fms_bank_list_excel b on m.erp_bank_id=b.collection_bank_id left join fms_kuan k on m.id=k.dkmassage_id "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); strSql1.Append(" where " + strWhere); } strSql.Append(" order by " + filedOrder + " limit " + (PageIndex - 1) * PageSize + "," + PageSize); Total = FMS_DbHelperMySQL.Query(strSql1.ToString()).Tables[0].Rows[0][0].ToString(); return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
///// <summary> ///// 获取记录总数 ///// </summary> //public int GetRecordCount(string strWhere) //{ // StringBuilder strSql = new StringBuilder(); // strSql.Append("select count(1) FROM fms_supply_bank "); // if (strWhere.Trim() != "") // { // strSql.Append(" where " + strWhere); // } // object obj = DbHelperSQL.GetSingle(strSql.ToString()); // if (obj == null) // { // return 0; // } // else // { // return Convert.ToInt32(obj); // } //} /// <summary> /// 分页获取数据列表 /// </summary> public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex) { StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT * FROM ( "); strSql.Append(" SELECT ROW_NUMBER() OVER ("); if (!string.IsNullOrEmpty(orderby.Trim())) { strSql.Append("order by T." + orderby); } else { strSql.Append("order by T.Id desc"); } strSql.Append(")AS Row, T.* from fms_supply_bank T "); if (!string.IsNullOrEmpty(strWhere.Trim())) { strSql.Append(" WHERE " + strWhere); } strSql.Append(" ) TT"); strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex); return(FMS_DbHelperMySQL.Query(strSql.ToString())); }
/// <summary> /// 得到一个对象实体 /// </summary> public FMS_Model.fms_bank_list_excel GetModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select id,collection_bank_id,collection_bank_code,collection_bank_name,collection_account,dk_time,dk_time_only,dk_money,dk_account,dk_username,dk_bank_name,dk_channel,dk_message_remarks,start_line,excel_exist_collection_account,sort,excel_type,is_use,is_show_full from fms_bank_list_excel,acc_type,is_collect "); strSql.Append(" where id=@id "); MySqlParameter[] parameters = { new MySqlParameter("@id", MySqlDbType.Int32, 11) }; parameters[0].Value = id; FMS_Model.fms_bank_list_excel model = new FMS_Model.fms_bank_list_excel(); DataSet ds = FMS_DbHelperMySQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
/// <summary> /// 得到一个对象实体 /// </summary> public FMS_Model.fms_refund_bill GetModel(int refund_id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select refund_id,refund_sn,refund_type,refund_status,emp_id,emp_code,emp_name,apply_date,user_id,customer_id,customer_name,refund_money,refund_capital_money,refund_remark,refund_bank_name,refund_bank_account,pay_method,pay_method_name,pay_id,pay_code,pay_name,pay_date,approvalOne_id,approvalOne_code,approvalOne_name,approvalOne_date,approvalTwo_id,approvalTwo_code,approvalTwo_name,approvalTwo_date,approvalThree_id,approvalThree_code,approvalThree_name,approvalThree_date,approvalFour_id,approvalFour_code,approvalFour_name,approvalFour_date,is_del,kingdee_current_account,is_send_out,kingdee_in_code,kingdee_out_code,print_num from fms_refund_bill "); strSql.Append(" where refund_id=@refund_id "); MySqlParameter[] parameters = { new MySqlParameter("@refund_id", MySqlDbType.Int32, 11) }; parameters[0].Value = refund_id; FMS_Model.fms_refund_bill model = new FMS_Model.fms_refund_bill(); DataSet ds = FMS_DbHelperMySQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
/// <summary> /// 得到一个对象实体 /// </summary> public FMS_Model.fms_ac_period GetModel(int Id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select Id,Factory_Id,C_customerid,C_customer_code,C_customername,C_userid,C_username,period_year,period_start,period_end,period_start_date,period_end_date,Is_stop,C_type from fms_ac_period "); strSql.Append(" where Id=@Id "); MySqlParameter[] parameters = { new MySqlParameter("@Id", MySqlDbType.Int32, 11) }; parameters[0].Value = Id; FMS_Model.fms_ac_period model = new FMS_Model.fms_ac_period(); DataSet ds = FMS_DbHelperMySQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
/// <summary> /// 得到一个对象实体 /// </summary> public FMS_Model.fms_supply_bank GetModel(int Id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select Id,factoryId,suppliers_id,suppliers_code,suppliers_name,supply_bank,supply_bankCode,pay_count,create_Id,create_code,create_name,create_date,Is_stop from fms_supply_bank "); strSql.Append(" where Id=@Id "); MySqlParameter[] parameters = { new MySqlParameter("@Id", MySqlDbType.Int32, 11) }; parameters[0].Value = Id; FMS_Model.fms_supply_bank model = new FMS_Model.fms_supply_bank(); DataSet ds = FMS_DbHelperMySQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
/// <summary> /// 得到一个对象实体 /// </summary> public FMS_Model.fms_kuan GetModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select id,jzdate,jytime,jiefang,daifang,name,number,user_qq,admin_name,admin_id,erp_dakuanhao,status,file_time,confirm_time,dkmassage_id,user_id from fms_kuan,acc_type "); strSql.Append(" where id=@id "); MySqlParameter[] parameters = { new MySqlParameter("@id", MySqlDbType.Int32, 11) }; parameters[0].Value = id; FMS_Model.fms_kuan model = new FMS_Model.fms_kuan(); DataSet ds = FMS_DbHelperMySQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
/// <summary> /// 得到一个对象实体 /// </summary> public FMS_Model.fms_dkmassage GetModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select id,datetime,price,account,user_id,user_name,channel,deal,stats,notes,is_delete,erp_dakuanhao,add_time,erp_bank_id,confirm_admin_id,confirm_time,erp_time,erp_type,acc_type from fms_dkmassage "); strSql.Append(" where id=@id "); MySqlParameter[] parameters = { new MySqlParameter("@id", MySqlDbType.Int32, 11) }; parameters[0].Value = id; FMS_Model.fms_dkmassage model = new FMS_Model.fms_dkmassage(); DataSet ds = FMS_DbHelperMySQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
/// <summary> /// 得到一个对象实体 /// </summary> public FMS_Model.fms_costitem_dep GetModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select id,dep_id,code_str,creater,create_time from fms_costitem_dep "); strSql.Append(" where id=@id "); MySqlParameter[] parameters = { new MySqlParameter("@id", MySqlDbType.Int32, 11) }; parameters[0].Value = id; FMS_Model.fms_costitem_dep model = new FMS_Model.fms_costitem_dep(); DataSet ds = FMS_DbHelperMySQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
/// <summary> /// 得到一个对象实体 /// </summary> public FMS_Model.fms_kuan_adjust GetModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select id,date_time,customer_id,user_id,change_total,change_type,change_desc,admin_id,erp_dakuanhao,erp_time,status,adjust_sn from fms_kuan_adjust "); strSql.Append(" where id=@id "); MySqlParameter[] parameters = { new MySqlParameter("@id", MySqlDbType.Int32, 11) }; parameters[0].Value = id; FMS_Model.fms_kuan_adjust model = new FMS_Model.fms_kuan_adjust(); DataSet ds = FMS_DbHelperMySQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }
/// <summary> /// 得到一个对象实体 /// </summary> public FMS_Model.fms_refund_log GetModel(long id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select id,refund_id,refund_sn,emp_id,emp_code,emp_name,log_info,log_time from fms_refund_log "); strSql.Append(" where id=@id "); MySqlParameter[] parameters = { new MySqlParameter("@id", MySqlDbType.Int32, 20) }; parameters[0].Value = id; FMS_Model.fms_refund_log model = new FMS_Model.fms_refund_log(); DataSet ds = FMS_DbHelperMySQL.Query(strSql.ToString(), parameters); if (ds.Tables[0].Rows.Count > 0) { return(DataRowToModel(ds.Tables[0].Rows[0])); } else { return(null); } }