/// <summary> /// 根据名称、税号、账户和开户行对公司模糊查询,查询结果为一笔或多笔数据 /// </summary> /// <param name="invoice_index"></param> /// <param name="company_name"></param> /// <returns></returns> public List <Invoice> SelectAll(int status, string start_time, string end_time, string confirm_start_time, string confirm_end_time, string invoice_index, string company_name, string invoice_company) { try { List <Invoice> objList = new List <Invoice>(); string sql = null; //if (!string.IsNullOrEmpty(company_name)) //{ // company_name = company_name.Replace("(", "\\(").Replace(")", "\\)"); //} sql = "select a.id,a.company_name,a.invoice_company,a.invoice_type,a.invoice_index," + "a.invoice_time,a.invoice_price,a.invoice_real_price,a.invoice_prepay,a.pay_type,a.remark,confirm_time " + "from jinchen.invoice_info a " + "where a.invoice_index ~* '{0}' and a.company_name ~* '{1}' and a.status={2} and to_char(invoice_time,'yyyy-MM-dd')>='{3}' " + "and to_char(invoice_time,'yyyy-MM-dd')<='{4}' and to_char(confirm_time,'yyyy-MM-dd')>='{5}' and to_char(confirm_time,'yyyy-MM-dd')<='{6}' and invoice_company ~* '{7}' " + "order by a.invoice_time desc"; sql = string.Format(sql, invoice_index, company_name, status, start_time, end_time, confirm_start_time, confirm_end_time, invoice_company); objList = PostgreHelper.GetEntityList <Invoice>(sql); return(objList); } catch (Exception ex) { throw ex; } }
/// <summary> /// 检查是否已经存在异常 /// </summary> /// <param name="SysTagCode"></param> /// <param name="MachineCode"></param> /// <param name="StationId"></param> /// <returns></returns> public bool CheckMaterialRequireExist(String SysTagCode, int MaterialId, string MachineCode = "", int StationId = -1) { string sql; if (StationId > 0) { sql = "select * from andon.error_log where station_id={0} and system_tag_code='{1}' and error_type_id='{2}' and (release_time<='2001-01-01 00:00:00' or release_time is null)"; sql = string.Format(sql, StationId, SysTagCode, MaterialId); } else { sql = "select * from andon.error_log where machine_code='{0}' and system_tag_code='{1}' and error_type_id='{2}' and (release_time<='2001-01-01 00:00:00' or release_time is null)"; sql = string.Format(sql, MachineCode, SysTagCode, MaterialId); } var list = PostgreHelper.GetEntityList <error_log>(sql); if (list == null || list.Count == 0) { return(false); } else { return(true); } }
/// <summary> /// 查询指定时间段内的记录 /// </summary> /// <param name="start_time">开始时间</param> /// <param name="end_time">结束时间</param> /// <param name="device_code">装置编号</param> /// <returns></returns> public List <error_log> GetAllLogByTime(DateTime start_time, DateTime end_time, string device_code = null) { List <error_log> list = new List <error_log>(); try { string sql; if (string.IsNullOrEmpty(device_code) == false) { sql = "select * from andon.error_log where start_time>='{0}' and start_time<='{1}' and machine_code='{2}'"; sql = string.Format(sql, start_time, end_time, device_code); } else { sql = "select * from andon.error_log where start_time>='{0}' and start_time<='{1}'"; sql = string.Format(sql, start_time, end_time); } list = PostgreHelper.GetEntityList <error_log>(sql); return(list); } catch (Exception ex) { return(list); } }
/// <summary> /// 查询入库单中尚未确认结款的单子(物料购入流水账和供应商结款共用一个) /// </summary> /// <param name="name"></param> /// <returns></returns> public List <Purchase> SelectDeliverAll(string start_time, string end_time, string company_name, string purchase_index, string material_name, string deliver_index, string category) { try { List <Purchase> objList = new List <Purchase>(); string sql = null; if (!string.IsNullOrEmpty(company_name)) { company_name = company_name.Replace("(", "\\(").Replace(")", "\\)"); } sql = "SELECT a.id, b.company_name, a.purchase_index, a.category,a.deliver_index,a.deliver_time, a.material_name, a.material_spec, " + "a.material_num, a.material_unit, a.material_price, a.material_all_price,money_onoff,money_way,status,confirm_time " + "FROM jinchen.purchase_info a, jinchen.company_info b " + " where a.supplier_id = b.id and a.purchase_index ~* '{0}' and b.company_name ~* '{1}' and a.material_name ~* '{2}' and flag=0 and " + "a.deliver_index ~* '{3}' and a.status =0 and to_char(a.deliver_time,'yyyy-MM-dd')>='{4}' and to_char(a.deliver_time,'yyyy-MM-dd')<='{5}' and a.category ~* '{6}' " + "order by a.purchase_index desc "; sql = string.Format(sql, purchase_index, company_name, material_name, deliver_index, start_time, end_time, category); objList = PostgreHelper.GetEntityList <Purchase>(sql); return(objList); } catch (Exception ex) { throw ex; } }
//更新数据 public int Update(material_request_info obj) { try { int count = 0; if (obj.depot_ack_time < Convert.ToDateTime("2000-01-01 00:00:00")) { string sql = "Update fimp.material_request_info set take_person_id={0}, take_time='{1}' where id={2}"; sql = string.Format(sql, obj.take_person_id, obj.take_time, obj.id); count = PostgreHelper.ExecuteNonQuery(sql); } else { string sql = "Update fimp.material_request_info set depot_ack_time='{0}' where id={1}"; sql = string.Format(sql, obj.depot_ack_time, obj.id); count = PostgreHelper.ExecuteNonQuery(sql); } return(count); } catch (Exception ex) { throw ex; } }
public List <dept_info> SelectAllid(int id) { try { List <dept_info> objList = new List <dept_info>(); string sql = null; if (id > 0) { sql = "SELECT * FROM fimp.dept_info where dept_id={0} order by dept_id"; sql = string.Format(sql, id); } else { sql = "SELECT * FROM fimp.dept_info order by plant_id"; } objList = PostgreHelper.GetEntityList <dept_info>(sql); return(objList); } catch (Exception ex) { throw ex; } }
/// <summary> /// 插入站别信息数据 /// </summary> /// <param name="obj"></param> /// <returns></returns> public int Insert(MachineInfo obj, int machine_id = 0) { try { string mqtt_no = obj.mqtt_no; if (string.IsNullOrEmpty(mqtt_no)) { mqtt_no = "A"; } string sql = null; if (machine_id <= 0) { sql = "Insert into fimp.machine_info(area_id,city_id,plant_id,unit_no,line_id,machine_code,machine_name_en,machine_name_tw,machine_name_cn,station_id,status_no,mqtt_no,set_up)values({0},{1},{2},'{3}',{4},'{5}','{6}',N'{7}',N'{8}',{9},'{10}','{11}',{12})"; sql = string.Format(sql, obj.area_id, obj.city_id, obj.plant_id, obj.unit_no, obj.line_id, obj.machine_code, obj.machine_name_en, obj.machine_name_tw, obj.machine_name_cn, obj.station_id, obj.status_no, mqtt_no, obj.set_up); } else { sql = "set IDENTITY_INSERT fimp.machine_info ON Insert into fimp.machine_info(area_id,city_id,plant_id,unit_no,line_id,machine_code,machine_name_en,machine_name_tw,machine_name_cn,station_id,status_no,mqtt_no,machine_id,set_up)values({0},{1},{2},'{3}',{4},'{5}','{6}',N'{7}',N'{8}',{9},'{10}','{11}',{12},{13}) set IDENTITY_INSERT fimp.machine_info OFF"; sql = string.Format(sql, obj.area_id, obj.city_id, obj.plant_id, obj.unit_no, obj.line_id, obj.machine_code, obj.machine_name_en, obj.machine_name_tw, obj.machine_name_cn, obj.station_id, obj.status_no, mqtt_no, machine_id, obj.set_up); } int count = PostgreHelper.ExecuteNonQuery(sql); return(count); } catch (Exception ex) { throw ex; } }
public List <rest_time_row> SelectAll(string start_time, string end_time) { try { List <rest_time_row> objList = new List <rest_time_row>(); string sql = null; if (!string.IsNullOrEmpty(start_time) && !string.IsNullOrEmpty(end_time)) { sql = "select * from fimp.rest_time_row where start_time>='{0}' and end_time<='{1}' order by start_time desc,machine_code"; sql = string.Format(sql, start_time, end_time); } else { string sys_time = System.DateTime.Now.AddHours(GlobalDefine.SysTimeZone).ToString("yyyy-MM-dd HH:mm:ss"); sql = "select * from fimp.rest_time_row where end_time>='{0}' order by start_time desc,machine_code"; sql = string.Format(sql, sys_time); } objList = PostgreHelper.GetEntityList <rest_time_row>(sql); return(objList); //string sql="select * from rest_time_row where " } catch (Exception ex) { throw ex; } }
/// <summary> /// 获取指定配置 /// </summary> /// <param name="system_tag_code">系统标签类型</param> /// <param name="unit_no">制程</param> /// <param name="line_id">线别,=-1则不启用</param> /// <returns>配置列表</returns> public error_config GetErrorConfigByCode(string system_tag_code, String unit_no, int line_id = -1) { error_config obj = new error_config(); string command = string.Empty; if (system_tag_code != null && line_id >= 0 && unit_no != null && unit_no.Length > 0) { command = string.Format("Select * from andon.error_config where system_tag_code='{0}' and unit_no='{1}' and line_id='{2}'", system_tag_code, unit_no, line_id); obj = PostgreHelper.GetSingleEntity <error_config>(command); if (obj == null)//查找不到,再向上一级查找 { command = string.Format("Select * from andon.error_config where system_tag_code='{0}' and unit_no='{1}'", system_tag_code, unit_no); } } else if (system_tag_code != null && unit_no != null && unit_no.Length > 0) { command = string.Format("Select * from andon.error_config where system_tag_code='{0}' and unit_no='{1}'", system_tag_code, unit_no); } if (command != string.Empty) { obj = PostgreHelper.GetSingleEntity <error_config>(command); return(obj); } return(null); }
public error_config_person SelectSingle(string class_no, int id) { try { error_config_person obj = new error_config_person(); string sql = null; if (!string.IsNullOrEmpty(class_no)) { sql = "select * from andon.error_config_person where class_no='{0}' order by class_no"; sql = string.Format(sql, class_no); } else { sql = "select * from andon.error_config_person where id={0} order by class_no"; sql = string.Format(sql, id); } obj = PostgreHelper.GetSingleEntity <error_config_person>(sql); return(obj); } catch (Exception ex) { throw ex; } }
public rest_time_row IsUpdate(string machine_code, string start_time, string end_time) { try { rest_time_row obj = new rest_time_row(); string sql = "select * from fimp.rest_time_row where machine_code='{0}' and start_time<='{1}' and end_time>='{2}'"; sql = string.Format(sql, machine_code, start_time, start_time); obj = PostgreHelper.GetSingleEntity <rest_time_row>(sql); if (obj != null) { } else { sql = "select * from fimp.rest_time_row where machine_code='{0}' and start_time<='{1}' and end_time>='{2}'"; sql = string.Format(sql, machine_code, end_time, end_time); obj = PostgreHelper.GetSingleEntity <rest_time_row>(sql); } return(obj); } catch (Exception ex) { throw ex; } }
public static int GetId() { string sql = "SELECT nextval('public_unit_id_seq')"; int id = Convert.ToInt32(PostgreHelper.GetField(PostGreSqlConnectionString, sql)); return(id); }
/// <summary> /// 查询时间是否在休息时间列表中 /// </summary> /// <param name="time">时间</param> /// <returns></returns> public bool SelectCount(string time, string unit_no) { try { bool result = false; string sql = "select * from fimp.rest_time where start_time<='{0}' and end_time>='{1}' and unit_no='{2}' and state='A'"; sql = string.Format(sql, time, time, unit_no); rest_time obj = PostgreHelper.GetSingleEntity <rest_time>(sql); if (obj != null) { result = true; } else { result = false; } return(result); } catch (Exception ex) { throw ex; } }
//查询单笔数据,通过名称 public line_info SelectSingleByName(string line_name) { string sql = "SELECT *FROM fimp.line_info where line_name_en='{0}'"; sql = string.Format(sql, line_name); return(PostgreHelper.GetSingleEntity <line_info>(sql)); }
/// <summary> /// 用于销售结款和历史销售结款 /// </summary> /// <param name="name"></param> /// <returns></returns> public List <DuiZhang> SelectHistory(string deliver_start_time, string deliver_end_time, string dz_start_time, string dz_end_time, string company_order_index, string company_name, string order_name, string dz_index, string invoice_index, string deliver_index) { try { List <DuiZhang> objList = new List <DuiZhang>(); string sql = null; if (!string.IsNullOrEmpty(company_name)) { company_name = company_name.Replace("(", "\\(").Replace(")", "\\)"); } sql = "select a.id, a.company_order_index,a.company_name,a.dui_num,a.dui_price,a.dui_all_price," + "a.order_name,a.unit,a.dz_index,a.invoice_index,a.deliver_time,dui_time,b.deliver_index " + "from (select * " + " from jinchen.duizhang_info a " + "where company_order_index ~*'{0}' and company_name ~*'{1}' and to_char(a.deliver_time,'yyyy-MM-dd')>='{2}' and " + "to_char(a.deliver_time,'yyyy-MM-dd')<='{3}' and to_char(a.dui_time,'yyyy-MM-dd')>='{4}' and to_char(a.dui_time,'yyyy-MM-dd')<='{5}' and " + "order_name ~* '{6}' and dz_index ~* '{7}' and invoice_index ~* '{8}' ) a " + "inner join (select * from jinchen.sale_info) b on a.sale_id=b.id and deliver_index ~* '{9}' " + "order by a.dui_time desc, b.deliver_index desc,a.company_name"; sql = string.Format(sql, company_order_index, company_name, deliver_start_time, deliver_end_time, dz_start_time, dz_end_time, order_name, dz_index, invoice_index, deliver_index); objList = PostgreHelper.GetEntityList <DuiZhang>(sql); return(objList); } catch (Exception ex) { throw ex; } }
public List <error_config> SelectAll(int line_id, string unit_no = null, string part_num = null) { List <error_config> list = new List <error_config>(); string command = string.Empty; if (line_id > 0 && unit_no != null && part_num != null) { command = string.Format("Select * from andon.error_config where line_id='{0}' and unit_no='{1}' and part_num='{2}' order by id", line_id, unit_no, part_num); } else if (line_id > 0 && unit_no != null) { command = string.Format("Select * from andon.error_config where line_id='{0}' and unit_no='{1}' order by id", line_id, unit_no); } else if (line_id > 0) { command = string.Format("Select * from andon.error_config where line_id='{0}' order by id", line_id); } else if (unit_no != null) { command = string.Format("Select * from andon.error_config where line_id='{0}' and unit_no='{1}' order by id", line_id, unit_no); } else { command = string.Format("Select * from andon.error_config order by id"); } if (command != string.Empty) { list = PostgreHelper.GetEntityList <error_config>(command); } return(list); }
public int Update(Attendance obj) { try { int count = 0; if (obj.id == 0) { DateTime time = DateTime.Now.AddHours(GlobalDefine.SysTimeZone); Decimal achievement = Convert.ToDecimal(obj.real_num) / Convert.ToDecimal(obj.standard_num); string sql = "insert into fimp.attendance(standard_num,real_num,attendance_rate,createtime,line_id,unit_no,class_no)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')"; sql = string.Format(sql, obj.standard_num, obj.real_num, achievement, time, obj.line_id, obj.unit_no, obj.class_no); count = PostgreHelper.ExecuteNonQuery(sql); return(count); } else { DateTime time = DateTime.Now.AddHours(GlobalDefine.SysTimeZone); Decimal achievement = Convert.ToDecimal(obj.real_num) / Convert.ToDecimal(obj.standard_num); string sql = "update fimp.attendance set standard_num='{0}',real_num='{1}',attendance_rate='{2}',createtime='{3}',line_id='{4}', unit_no='{5}',class_no='{6}' where id={7}"; sql = string.Format(sql, obj.standard_num, obj.real_num, achievement, time, obj.line_id, obj.unit_no, obj.class_no, obj.id); count = PostgreHelper.ExecuteNonQuery(sql); return(count); } } catch (Exception ex) { throw ex; } }
/// <summary> /// 用于销售管理:模糊查询,查询结果为一笔或多笔数据 /// </summary> /// <param name="name"></param> /// <returns></returns> public List <DuiZhang> SelectForInvoice(string dz_start_time, string dz_end_time, string deliver_company_head, string dz_index) { try { List <DuiZhang> objList = new List <DuiZhang>(); string sql = null; if (!string.IsNullOrEmpty(deliver_company_head)) { deliver_company_head = deliver_company_head.Replace("(", "\\(").Replace(")", "\\)"); } sql = "select a.dz_index,max(a.dui_time) as dui_time,max(a.company_name) as company_name," + " sum(a.dui_num) as dui_num,sum(a.dui_all_price) as dui_all_price" + " from jinchen.duizhang_info a, jinchen.sale_info b" + " where a.sale_id = b.id and company_name ~*'{0}'" + " and to_char(a.dui_time,'yyyy-MM-dd')>= '{1}' and to_char(a.dui_time,'yyyy-MM-dd')<= '{2}'" + " and dz_index ~*'{3}'" + " and a.invoice_time is null" + " group by a.dz_index" + " order by a.dz_index desc"; sql = string.Format(sql, deliver_company_head, dz_start_time, dz_end_time, dz_index); objList = PostgreHelper.GetEntityList <DuiZhang>(sql); return(objList); } catch (Exception ex) { throw ex; } }
public static bool CreateAdmin(Admin info) { string sql = @"insert into sys_admin (username, openid, realname, create_time, last_login_time, last_login_ip, roles_name, role_list, role_name_list, isvalid) values (@username, @openid, @realname, @create_time, @last_login_time, @last_login_ip, @roles_name, @role_list, @role_name_list, @isvalid)"; DbParameter[] parms = { PostgreHelper.MakeInParam("@username", NpgsqlDbType.Varchar, 50, info.username), PostgreHelper.MakeInParam("@openid", NpgsqlDbType.Varchar, 50, info.openid), PostgreHelper.MakeInParam("@realname", NpgsqlDbType.Varchar, 20, info.realname), PostgreHelper.MakeInParam("@create_time", NpgsqlDbType.TimestampTZ, -1, info.create_time), PostgreHelper.MakeInParam("@last_login_time", NpgsqlDbType.TimestampTZ, -1, info.last_login_time), PostgreHelper.MakeInParam("@last_login_ip", NpgsqlDbType.Varchar, 15, info.last_login_ip), PostgreHelper.MakeInParam("@roles_name", NpgsqlDbType.Varchar, 200, info.roles_name), PostgreHelper.MakeInParam("@role_list", NpgsqlDbType.Array | NpgsqlDbType.Integer, 200, info.role_list), PostgreHelper.MakeInParam("@role_name_list", NpgsqlDbType.Array | NpgsqlDbType.Varchar, 200, info.role_name_list), PostgreHelper.MakeInParam("@isvalid", NpgsqlDbType.Boolean, -1, info.isvalid), }; int rlt = PostgreHelper.ExecuteNonQuery(PostGreSqlConnectionString, CommandType.Text, sql, parms); return(rlt > 0); }
//查询所有 public List <material_info> SelectAll(string material_name) { try { List <material_info> objList = new List <material_info>(); string sql = null; if (string.IsNullOrEmpty(material_name)) { sql = "SELECT a.id,a.category_id,b.type_name,a.material_code,a.material_name,a.material_type,a.material_inventory,a.remark,a.createtime FROM fimp.material_info a,fimp.material_category b where a.category_id=b.id order by a.material_code, a.material_code"; sql = string.Format(sql); } else { sql = "SELECT a.id,a.category_id,b.type_name,a.material_code,a.material_name,a.material_type,a.material_inventory,a.remark,a.createtime FROM fimp.material_info a,fimp.material_category b where a.category_id=b.id and a.material_name like '%{0}%' or a.material_code like '%{0}%' order by a.material_code, a.material_code"; sql = string.Format(sql, material_name); } objList = PostgreHelper.GetEntityList <material_info>(sql); return(objList); } catch (Exception ex) { throw ex; } }
/// <summary> /// 用于订单管理:根据联系人名模糊查询,查询结果为一笔或多笔数据 /// </summary> /// <param name="name"></param> /// <returns></returns> public List <Order> SelectAll(int order_status, string start_time, string end_time, string deliver_start_time, string deliver_end_time, string company_name, string order_index, string company_order_index, string purchase_person, string order_name) { try { List <Order> objList = new List <Order>(); string sql = null; if (!string.IsNullOrEmpty(company_name)) { company_name = company_name.Replace("(", "\\(").Replace(")", "\\)"); } sql = "select c.id,c.company_order_index,c.order_index,b.company_name,a.order_time,a.order_name,a.deliver_time," + " order_num,order_price,order_all_price,tui_num, open_num,remain_num,purchase_person,a.seq_id,a.order_picture,a.remark,a.unit " + "from jinchen.orderseq_info a,jinchen.company_info b,jinchen.order_info c where a.order_id=c.id and c.customer_id=b.id and a.flag=0 and " + " b.company_name ~* '{0}' and c.order_index ~* '{1}' and c.company_order_index ~*'{2}' and to_char(a.order_time,'yyyy-MM-dd')>='{3}' and to_char(a.order_time,'yyyy-MM-dd')<='{4}' and a.order_status={5} " + " and to_char(a.deliver_time,'yyyy-MM-dd')>='{6}' and to_char(a.deliver_time,'yyyy-MM-dd')<='{7}' and purchase_person ~*'{8}' and order_name ~*'{9}' " + " order by a.order_time desc,c.order_index desc,order_name"; sql = string.Format(sql, company_name, order_index, company_order_index, start_time, end_time, order_status, deliver_start_time, deliver_end_time, purchase_person, order_name); objList = PostgreHelper.GetEntityList <Order>(sql); return(objList); } catch (Exception ex) { throw ex; } }
public static long GetId() { string sql = "SELECT nextval('public_sys_admin_id_seq')"; long id = Convert.ToInt64(PostgreHelper.GetField(PostGreSqlConnectionString, sql)); return(id); }
public static bool Createcomment(Comment info) { string sql = @"insert into article_comment (article_id, comm_content, comm_username, comm_time, ip, auditor, audit_time, is_valid, shield_words, forum_topicid, forum_threadid, last_modifier, modify_time) values (@article_id, @comm_content, @comm_username, @comm_time, @ip, @auditor, @audit_time, @is_valid, @shield_words, @forum_topicid, @forum_threadid, @last_modifier, @modify_time)"; DbParameter[] parms = { PostgreHelper.MakeInParam("@article_id", NpgsqlDbType.Bigint, -1, info.article_id), PostgreHelper.MakeInParam("@comm_content", NpgsqlDbType.Text, -1, info.comm_content), PostgreHelper.MakeInParam("@comm_username", NpgsqlDbType.Varchar, 50, info.comm_username), PostgreHelper.MakeInParam("@comm_time", NpgsqlDbType.TimestampTZ, -1, info.comm_time), PostgreHelper.MakeInParam("@ip", NpgsqlDbType.Varchar, 15, info.ip), PostgreHelper.MakeInParam("@auditor", NpgsqlDbType.Varchar, 50, info.auditor), PostgreHelper.MakeInParam("@audit_time", NpgsqlDbType.TimestampTZ, -1, info.audit_time), PostgreHelper.MakeInParam("@is_valid", NpgsqlDbType.Boolean, -1, info.is_valid), PostgreHelper.MakeInParam("@shield_words", NpgsqlDbType.Varchar, 200, info.shield_words), PostgreHelper.MakeInParam("@forum_topicid", NpgsqlDbType.Integer, -1, info.forum_topicid), PostgreHelper.MakeInParam("@forum_threadid", NpgsqlDbType.Integer, -1, info.forum_threadid), PostgreHelper.MakeInParam("@last_modifier", NpgsqlDbType.Varchar, 50, info.last_modifier), PostgreHelper.MakeInParam("@modify_time", NpgsqlDbType.TimestampTZ, -1, info.modify_time), }; int rlt = PostgreHelper.ExecuteNonQuery(PostGreSqlConnectionString, CommandType.Text, sql, parms); return(rlt > 0); }
/// <summary> /// 查询订单下的序号,只用于判断该订单下的序号有多少个 /// </summary> /// <param name="id">用户id</param> /// <returns></returns> public List <Order> SelectOrderSeqList(int order_id, string company_name) { try { List <Order> objList = new List <Order>(); string sql = null; if (order_id == 0) { sql = "select a.id,c.order_id,a.order_index,b.company_name, c.seq_id,c.order_time,c.order_name,c.unit,c.deliver_time," + "c.order_num,c.open_num,c.tui_num,c.remain_num,c.order_price,c.order_all_price,c.purchase_person,c.remark,a.company_order_index " + "from jinchen.order_info a,jinchen.company_info b,jinchen.orderseq_info c " + "where a.customer_id=b.id and a.id=c.order_id and b.company_name='{0}' and c.flag=0 order by c.order_name"; sql = string.Format(sql, company_name); } else { sql = "select a.id,c.order_id,a.order_index,b.company_name, c.seq_id,c.order_time,c.order_name,c.unit,c.deliver_time," + "c.order_num,c.open_num,c.tui_num,c.remain_num,c.order_price,c.order_all_price,c.purchase_person,c.remark,a.company_order_index " + "from jinchen.order_info a,jinchen.company_info b,jinchen.orderseq_info c " + "where a.customer_id=b.id and a.id=c.order_id and a.id={0} and b.company_name='{1}' and c.flag=0 order by c.order_name"; sql = string.Format(sql, order_id, company_name); } objList = PostgreHelper.GetEntityList <Order>(sql); return(objList); } catch (Exception ex) { throw ex; } }
public List <error_log_person> GetErrorLogPersonByLogId(int LogId) { string command = string.Format("Select * from andon.error_log_person where error_log_id='{0}'", LogId); var list = PostgreHelper.GetEntityList <error_log_person>(command); return(list); }
/// <summary> /// 退单统计数据(待确认和已确认) /// </summary> /// <param name="name"></param> /// <returns></returns> public List <SaleReturn> SelectAll(int return_status, string start_time, string end_time, string return_index, string order_name) { try { List <SaleReturn> objList = new List <SaleReturn>(); string sql = null; sql = "select a.return_index,a.return_num,a.return_price,a.return_all_price,a.insert_time,b.deliver_index,c.order_name,c.unit," + "d.company_order_index,b.deliver_company_head,a.seq_id,a.confirm_time " + "from jinchen.salereturn_info a,jinchen.sale_info b,jinchen.orderseq_info c,jinchen.order_info d " + " where a.deliver_index=b.deliver_index and a.seq_id=b.seq_id " + "and b.seq_id=c.seq_id and b.order_id=c.order_id and c.order_id=d.id and " + " return_index ~*'{0}' and to_char(a.insert_time,'yyyy-MM-dd') >='{1}' and " + "to_char(a.insert_time,'yyyy-MM-dd') <='{2}' and return_status={3} and c.order_name ~* '{4}' " + "order by return_index desc"; sql = string.Format(sql, return_index, start_time, end_time, return_status, order_name); objList = PostgreHelper.GetEntityList <SaleReturn>(sql); return(objList); } catch (Exception ex) { throw ex; } }
/// <summary> /// 用于销售结款和历史销售结款 /// </summary> /// <param name="name"></param> /// <returns></returns> public List <Sale> SelectMoneyAll(string start_time, string end_time, string deliver_index, string deliver_company_head, string order_name, string purchase_person) { try { List <Sale> objList = new List <Sale>(); string sql = null; if (!string.IsNullOrEmpty(deliver_company_head)) { deliver_company_head = deliver_company_head.Replace("(", "\\(").Replace(")", "\\)"); } sql = "select a.id, b.company_order_index,a.deliver_index,a.deliver_company_head,real_num,deliver_price,deliver_all_price," + "a.money_way,order_name,unit,purchase_person,a.insert_time,a.seq_id,a.money_onoff,c.tui_num,a.return_flag,a.dz_num " + " from jinchen.sale_info a,jinchen.order_info b,jinchen.orderseq_info c " + "where a.order_id=b.id and a.seq_id=c.seq_id and a.order_id=c.order_id and deliver_index ~*'{0}' and deliver_company_head ~*'{1}' and to_char(a.insert_time,'yyyy-MM-dd')>='{2}' and " + "to_char(a.insert_time,'yyyy-MM-dd')<='{3}' and order_name ~* '{4}' and purchase_person ~* '{5}' and a.flag=0 and c.flag=0 " + "order by deliver_index desc,deliver_company_head"; sql = string.Format(sql, deliver_index, deliver_company_head, start_time, end_time, order_name, purchase_person); objList = PostgreHelper.GetEntityList <Sale>(sql); return(objList); } catch (Exception ex) { throw ex; } }
/// <summary> /// 用于销售管理:模糊查询,查询结果为一笔或多笔数据 /// </summary> /// <param name="name"></param> /// <returns></returns> public List <Sale> SelectAll(string start_time, string end_time, string deliver_index, string deliver_company_head) { try { List <Sale> objList = new List <Sale>(); string sql = null; if (!string.IsNullOrEmpty(deliver_company_head)) { deliver_company_head = deliver_company_head.Replace("(", "\\(").Replace(")", "\\)"); } sql = "select deliver_index,deliver_company_head,sum(real_num) as real_num,sum(deliver_all_price) as deliver_all_price,max(insert_time) as insert_time " + "from jinchen.sale_info a " + "where deliver_index ~*'{0}' and deliver_company_head ~*'{1}' and to_char(insert_time,'yyyy-MM-dd')>='{2}' and to_char(insert_time,'yyyy-MM-dd')<='{3}' and a.flag=0 " + "group by deliver_index,deliver_company_head order by deliver_index desc"; sql = string.Format(sql, deliver_index, deliver_company_head, start_time, end_time); objList = PostgreHelper.GetEntityList <Sale>(sql); return(objList); } catch (Exception ex) { throw ex; } }
/// <summary> /// 获取最后一笔数据 /// </summary> /// <param name="machine_code">设备编号</param> /// <param name="end_time">结束时间</param> /// <returns></returns> public CT SelectSingle(string machine_code, string date_time) { try { CT obj = new CT(); string comm = null; if (string.IsNullOrEmpty(date_time)) { comm = "select id,machine_code,station_id,pn,wo,start_time,end_time,value,tag_code from fimp.ct where machine_code='{0}' order by end_time desc limit 1"; comm = string.Format(comm, machine_code); } else { string end_time = Convert.ToDateTime(date_time).ToString("yyyy-MM-dd HH:mm:ss.999"); comm = "select id,machine_code,station_id,pn,wo,start_time,end_time,value,tag_code from fimp.ct where machine_code='{0}' and end_time>'{1}' order by end_time desc limit 1"; comm = string.Format(comm, machine_code, end_time); } obj = PostgreHelper.GetSingleEntity <CT>(comm); return(obj); } catch (Exception ex) { Console.WriteLine(ex.Message); } return(null); }
/// <summary> /// 查询所有C/T,或者指定设备的 /// </summary> /// <returns></returns> public List <CT> SelectAllByWorkOrder(string device_code, string work_order, string pn, string tag_code) { try { List <CT> objList = new List <CT>(); string comm; if (string.IsNullOrEmpty(tag_code) == false) { comm = "SELECT * FROM fimp.ct where machine_code='{0}' and wo='{1}' and pn='{2}' and tag_code='{3}' order by end_time desc"; comm = string.Format(comm, device_code, work_order, pn, tag_code); } else { comm = "SELECT * FROM fimp.ct where machine_code='{0}' and wo='{1}' and pn='{2}' order by end_time desc"; comm = string.Format(comm, device_code, work_order, pn); } objList = PostgreHelper.GetEntityList <CT>(comm); return(objList); } catch (Exception ex) { Console.WriteLine(ex.Message); } return(null); }
protected override void OnLoad(EventArgs e) { base.OnLoad(e); IDBHelper dbHelper = new PostgreHelper(); string connectionString = "User ID=postgres;Password=admin;Server=192.168.0.226;Port=5432;Database=MonitorDB;"; string sql = "insert into [RawLog]([ProjectID],[File],[Note]) values('项目编号',@file,'备注')"; string file = this.textBox1.Text; if (!File.Exists(file)) return; //获取文件二进制流 System.IO.FileStream fs = new System.IO.FileStream(file, System.IO.FileMode.Open); BinaryReader br = new BinaryReader(fs); byte[] b = br.ReadBytes((int)fs.Length); fs.Close(); int r = dbHelper.ExecuteNonQuery(connectionString, CommandType.Text, sql, new Npgsql.NpgsqlParameter("@file", raw_log.File)); }