/// <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));
}
