You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
864 lines
41 KiB
C#
864 lines
41 KiB
C#
using System;
|
|
using System.Data;
|
|
using System.Collections.Generic;
|
|
using MySql.Data.MySqlClient;
|
|
using CommonFunc;
|
|
using System.Text;
|
|
using XGL.Models;
|
|
namespace XGL.Data
|
|
{
|
|
public class basedata_orderplanDb : IDisposable
|
|
{
|
|
public void Dispose()
|
|
{
|
|
Dispose(true);
|
|
GC.SuppressFinalize(true);
|
|
}
|
|
protected virtual void Dispose(bool disposing)
|
|
{
|
|
if (!disposing)
|
|
return;
|
|
}
|
|
///<summary>
|
|
///判断是否存在记录
|
|
///</summary>
|
|
public bool Exists(int id)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("select count(1) from role");
|
|
strSql.Append(" where ");
|
|
strSql.Append(" id = @id ");
|
|
MySqlParameter[] parameters = {
|
|
new MySqlParameter("@id", MySqlDbType.Int32)
|
|
};
|
|
parameters[0].Value = id;
|
|
return DbHelperSQLServer.Exists(strSql.ToString(), parameters);
|
|
}
|
|
/// <summary>
|
|
/// 增加一条数据
|
|
/// </summary>
|
|
public int Add(basedata_orderplanModel model)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("insert into basedata_orderplan(");
|
|
strSql.Append("OrderNo,ProductCode,ProductDesc,Amount,OrderStartDate,OrderEndDate,PlanWeek,PlanDate,PlanType,PlanOrder,PlanState,PlanStartTime,UpAmount,DownAount,Version,IsDeleted,CreateTime,CreateId,CreateBy,ModifyTime,ModifyId,ModifyBy)");
|
|
strSql.Append(" values (");
|
|
strSql.Append("@OrderNo,@ProductCode,@ProductDesc,@Amount,@OrderStartDate,@OrderEndDate,@PlanWeek,@PlanDate,@PlanType,@PlanOrder,@PlanState,@PlanStartTime,@UpAmount,@DownAount,@Version,@IsDeleted,@CreateTime,@CreateId,@CreateBy,@ModifyTime,@ModifyId,@ModifyBy)");
|
|
strSql.Append("; select @@IDENTITY");
|
|
MySqlParameter[] parameters = {
|
|
new MySqlParameter("@Id",model.Id),
|
|
new MySqlParameter("@OrderNo",model.OrderNo),
|
|
new MySqlParameter("@ProductCode",model.ProductCode),
|
|
new MySqlParameter("@ProductDesc",model.ProductDesc),
|
|
new MySqlParameter("@Amount",model.Amount),
|
|
new MySqlParameter("@OrderStartDate",model.OrderStartDate),
|
|
new MySqlParameter("@OrderEndDate",model.OrderEndDate),
|
|
new MySqlParameter("@PlanWeek",model.PlanWeek),
|
|
new MySqlParameter("@PlanDate",model.PlanDate),
|
|
new MySqlParameter("@PlanType",model.PlanType),
|
|
new MySqlParameter("@PlanOrder",model.PlanOrder),
|
|
new MySqlParameter("@PlanState",model.PlanState),
|
|
new MySqlParameter("@PlanStartTime",model.PlanStartTime),
|
|
new MySqlParameter("@UpAmount",model.UpAmount),
|
|
new MySqlParameter("@DownAount",model.DownAount),
|
|
new MySqlParameter("@Version",model.Version),
|
|
new MySqlParameter("@IsDeleted",model.IsDeleted),
|
|
new MySqlParameter("@CreateTime",model.CreateTime),
|
|
new MySqlParameter("@CreateId",model.CreateId),
|
|
new MySqlParameter("@CreateBy",model.CreateBy),
|
|
new MySqlParameter("@ModifyTime",model.ModifyTime),
|
|
new MySqlParameter("@ModifyId",model.ModifyId),
|
|
new MySqlParameter("@ModifyBy",model.ModifyBy),
|
|
};
|
|
object obj = DbHelperSQLServer.ExecuteScalar(strSql.ToString(), parameters);
|
|
if (obj == null)
|
|
{
|
|
return 0;
|
|
}
|
|
else
|
|
{
|
|
return Convert.ToInt32(obj);
|
|
}
|
|
}
|
|
/// </summary>
|
|
/// 修改一条数据
|
|
/// </summary>
|
|
bool Update(basedata_orderplanModel model)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("update basedata_orderplan set");
|
|
strSql.Append(" OrderNo = @OrderNo , ");
|
|
strSql.Append(" ProductCode = @ProductCode , ");
|
|
strSql.Append(" ProductDesc = @ProductDesc , ");
|
|
strSql.Append(" Amount = @Amount , ");
|
|
strSql.Append(" OrderStartDate = @OrderStartDate , ");
|
|
strSql.Append(" OrderEndDate = @OrderEndDate , ");
|
|
strSql.Append(" PlanWeek = @PlanWeek , ");
|
|
strSql.Append(" PlanDate = @PlanDate , ");
|
|
strSql.Append(" PlanType = @PlanType , ");
|
|
strSql.Append(" PlanOrder = @PlanOrder , ");
|
|
strSql.Append(" PlanState = @PlanState , ");
|
|
strSql.Append(" PlanStartTime = @PlanStartTime , ");
|
|
strSql.Append(" UpAmount = @UpAmount , ");
|
|
strSql.Append(" DownAount = @DownAount , ");
|
|
strSql.Append(" Version = @Version , ");
|
|
strSql.Append(" IsDeleted = @IsDeleted , ");
|
|
strSql.Append(" CreateTime = @CreateTime , ");
|
|
strSql.Append(" CreateId = @CreateId , ");
|
|
strSql.Append(" CreateBy = @CreateBy , ");
|
|
strSql.Append(" ModifyTime = @ModifyTime , ");
|
|
strSql.Append(" ModifyId = @ModifyId , ");
|
|
strSql.Append(" ModifyBy = @ModifyBy");
|
|
strSql.Append(" where Id = @Id");
|
|
MySqlParameter[] parameters = {
|
|
new MySqlParameter("@Id", model.Id),
|
|
new MySqlParameter("@OrderNo", model.OrderNo),
|
|
new MySqlParameter("@ProductCode", model.ProductCode),
|
|
new MySqlParameter("@ProductDesc", model.ProductDesc),
|
|
new MySqlParameter("@Amount", model.Amount),
|
|
new MySqlParameter("@OrderStartDate", model.OrderStartDate),
|
|
new MySqlParameter("@OrderEndDate", model.OrderEndDate),
|
|
new MySqlParameter("@PlanWeek", model.PlanWeek),
|
|
new MySqlParameter("@PlanDate", model.PlanDate),
|
|
new MySqlParameter("@PlanType", model.PlanType),
|
|
new MySqlParameter("@PlanOrder", model.PlanOrder),
|
|
new MySqlParameter("@PlanState", model.PlanState),
|
|
new MySqlParameter("@PlanStartTime", model.PlanStartTime),
|
|
new MySqlParameter("@UpAmount", model.UpAmount),
|
|
new MySqlParameter("@DownAount", model.DownAount),
|
|
new MySqlParameter("@Version", model.Version),
|
|
new MySqlParameter("@IsDeleted", model.IsDeleted),
|
|
new MySqlParameter("@CreateTime", model.CreateTime),
|
|
new MySqlParameter("@CreateId", model.CreateId),
|
|
new MySqlParameter("@CreateBy", model.CreateBy),
|
|
new MySqlParameter("@ModifyTime", model.ModifyTime),
|
|
new MySqlParameter("@ModifyId", model.ModifyId),
|
|
new MySqlParameter("@ModifyBy", model.ModifyBy),
|
|
};
|
|
int rows = DbHelperSQLServer.ExecuteNonQuery(strSql.ToString(), parameters);
|
|
if (rows > 0)
|
|
{
|
|
return true;
|
|
}
|
|
else
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 删除一条数据
|
|
/// </summary>
|
|
public bool Delete(int id)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("delete from role ");
|
|
strSql.Append(" where id=@id");
|
|
MySqlParameter[] parameters = {
|
|
new MySqlParameter("@id", MySqlDbType.Int32)
|
|
};
|
|
parameters[0].Value = id;
|
|
int rows = DbHelperSQLServer.ExecuteNonQuery(strSql.ToString(), parameters);
|
|
if (rows > 0)
|
|
{
|
|
return true;
|
|
}
|
|
else
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 得到一个对象实体
|
|
/// </summary>
|
|
public basedata_orderplanModel GetModel(int id)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("select");
|
|
strSql.Append(" Id, OrderNo, ProductCode, ProductDesc, Amount, DATE_FORMAT(OrderStartDate,'%Y-%m-%d %H:%i:%s') OrderStartDate, DATE_FORMAT(OrderEndDate,'%Y-%m-%d %H:%i:%s') OrderEndDate, PlanWeek, DATE_FORMAT(PlanDate,'%Y-%m-%d %H:%i:%s') PlanDate, PlanType, PlanOrder, PlanState, DATE_FORMAT(PlanStartTime,'%Y-%m-%d %H:%i:%s') PlanStartTime, UpAmount, DownAount, Version, IsDeleted, DATE_FORMAT(CreateTime,'%Y-%m-%d %H:%i:%s') CreateTime, CreateId, CreateBy, DATE_FORMAT(ModifyTime,'%Y-%m-%d %H:%i:%s') ModifyTime, ModifyId, ModifyBy");
|
|
strSql.Append(" from basedata_orderplan");
|
|
strSql.Append(" where id=@id");
|
|
MySqlParameter[] parameters = {
|
|
new MySqlParameter("@id", MySqlDbType.Int32)
|
|
};
|
|
parameters[0].Value = id;
|
|
basedata_orderplanModel model = new basedata_orderplanModel();
|
|
DataSet ds = DbHelperSQLServer.Query(strSql.ToString(), parameters);
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
if (ds.Tables[0].Rows[0]["Id"] != DBNull.Value)
|
|
{
|
|
model.Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["OrderNo"] != DBNull.Value)
|
|
{
|
|
model.OrderNo = Convert.ToString(ds.Tables[0].Rows[0]["OrderNo"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["ProductCode"] != DBNull.Value)
|
|
{
|
|
model.ProductCode = Convert.ToString(ds.Tables[0].Rows[0]["ProductCode"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["ProductDesc"] != DBNull.Value)
|
|
{
|
|
model.ProductDesc = Convert.ToString(ds.Tables[0].Rows[0]["ProductDesc"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["Amount"] != DBNull.Value)
|
|
{
|
|
model.Amount = Convert.ToInt32(ds.Tables[0].Rows[0]["Amount"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["OrderStartDate"] != DBNull.Value)
|
|
{
|
|
model.OrderStartDate = Convert.ToString(ds.Tables[0].Rows[0]["OrderStartDate"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["OrderEndDate"] != DBNull.Value)
|
|
{
|
|
model.OrderEndDate = Convert.ToString(ds.Tables[0].Rows[0]["OrderEndDate"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["PlanWeek"] != DBNull.Value)
|
|
{
|
|
model.PlanWeek = Convert.ToInt32(ds.Tables[0].Rows[0]["PlanWeek"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["PlanDate"] != DBNull.Value)
|
|
{
|
|
model.PlanDate = Convert.ToString(ds.Tables[0].Rows[0]["PlanDate"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["PlanType"] != DBNull.Value)
|
|
{
|
|
model.PlanType = Convert.ToString(ds.Tables[0].Rows[0]["PlanType"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["PlanOrder"] != DBNull.Value)
|
|
{
|
|
model.PlanOrder = Convert.ToInt32(ds.Tables[0].Rows[0]["PlanOrder"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["PlanState"] != DBNull.Value)
|
|
{
|
|
model.PlanState = Convert.ToInt32(ds.Tables[0].Rows[0]["PlanState"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["PlanStartTime"] != DBNull.Value)
|
|
{
|
|
model.PlanStartTime = Convert.ToString(ds.Tables[0].Rows[0]["PlanStartTime"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["UpAmount"] != DBNull.Value)
|
|
{
|
|
model.UpAmount = Convert.ToInt32(ds.Tables[0].Rows[0]["UpAmount"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["DownAount"] != DBNull.Value)
|
|
{
|
|
model.DownAount = Convert.ToInt32(ds.Tables[0].Rows[0]["DownAount"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["Version"] != DBNull.Value)
|
|
{
|
|
model.Version = Convert.ToString(ds.Tables[0].Rows[0]["Version"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["IsDeleted"] != DBNull.Value)
|
|
{
|
|
model.IsDeleted = ds.Tables[0].Rows[0]["IsDeleted"].ToString() == "False" ? false : true;
|
|
}
|
|
if (ds.Tables[0].Rows[0]["CreateTime"] != DBNull.Value)
|
|
{
|
|
model.CreateTime = Convert.ToString(ds.Tables[0].Rows[0]["CreateTime"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["CreateId"] != DBNull.Value)
|
|
{
|
|
model.CreateId = Convert.ToInt32(ds.Tables[0].Rows[0]["CreateId"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["CreateBy"] != DBNull.Value)
|
|
{
|
|
model.CreateBy = Convert.ToString(ds.Tables[0].Rows[0]["CreateBy"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["ModifyTime"] != DBNull.Value)
|
|
{
|
|
model.ModifyTime = Convert.ToString(ds.Tables[0].Rows[0]["ModifyTime"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["ModifyId"] != DBNull.Value)
|
|
{
|
|
model.ModifyId = Convert.ToInt32(ds.Tables[0].Rows[0]["ModifyId"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["ModifyBy"] != DBNull.Value)
|
|
{
|
|
model.ModifyBy = Convert.ToString(ds.Tables[0].Rows[0]["ModifyBy"]);
|
|
}
|
|
return model;
|
|
}
|
|
else
|
|
{
|
|
return null;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 得到一个对象实体集合
|
|
/// </summary>
|
|
public List<basedata_orderplanModel> GetList(string strWhere)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("select");
|
|
strSql.Append(" Id, OrderNo, ProductCode, ProductDesc, Amount, DATE_FORMAT(OrderStartDate,'%Y-%m-%d %H:%i:%s') OrderStartDate, DATE_FORMAT(OrderEndDate,'%Y-%m-%d %H:%i:%s') OrderEndDate, PlanWeek, DATE_FORMAT(PlanDate,'%Y-%m-%d %H:%i:%s') PlanDate, PlanType, PlanOrder, PlanState, DATE_FORMAT(PlanStartTime,'%Y-%m-%d %H:%i:%s') PlanStartTime, UpAmount, DownAount, Version, IsDeleted, DATE_FORMAT(CreateTime,'%Y-%m-%d %H:%i:%s') CreateTime, CreateId, CreateBy, DATE_FORMAT(ModifyTime,'%Y-%m-%d %H:%i:%s') ModifyTime, ModifyId, ModifyBy");
|
|
strSql.Append(" from basedata_orderplan");
|
|
if (!string.IsNullOrWhiteSpace(strWhere))
|
|
strSql.Append(" where " + strWhere);
|
|
List<basedata_orderplanModel> Items = new List<basedata_orderplanModel>();
|
|
DataSet ds = DbHelperSQLServer.Query(strSql.ToString());
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
foreach (DataRow row in ds.Tables[0].Rows)
|
|
{
|
|
basedata_orderplanModel model = new basedata_orderplanModel();
|
|
if (row["Id"] != DBNull.Value)
|
|
{
|
|
model.Id = Convert.ToInt32(row["Id"]);
|
|
}
|
|
if (row["OrderNo"] != DBNull.Value)
|
|
{
|
|
model.OrderNo = Convert.ToString(row["OrderNo"]);
|
|
}
|
|
if (row["ProductCode"] != DBNull.Value)
|
|
{
|
|
model.ProductCode = Convert.ToString(row["ProductCode"]);
|
|
}
|
|
if (row["ProductDesc"] != DBNull.Value)
|
|
{
|
|
model.ProductDesc = Convert.ToString(row["ProductDesc"]);
|
|
}
|
|
if (row["Amount"] != DBNull.Value)
|
|
{
|
|
model.Amount = Convert.ToInt32(row["Amount"]);
|
|
}
|
|
if (row["OrderStartDate"] != DBNull.Value)
|
|
{
|
|
model.OrderStartDate = Convert.ToString(row["OrderStartDate"]);
|
|
}
|
|
if (row["OrderEndDate"] != DBNull.Value)
|
|
{
|
|
model.OrderEndDate = Convert.ToString(row["OrderEndDate"]);
|
|
}
|
|
if (row["PlanWeek"] != DBNull.Value)
|
|
{
|
|
model.PlanWeek = Convert.ToInt32(row["PlanWeek"]);
|
|
}
|
|
if (row["PlanDate"] != DBNull.Value)
|
|
{
|
|
model.PlanDate = Convert.ToString(row["PlanDate"]);
|
|
}
|
|
if (row["PlanType"] != DBNull.Value)
|
|
{
|
|
model.PlanType = Convert.ToString(row["PlanType"]);
|
|
}
|
|
if (row["PlanOrder"] != DBNull.Value)
|
|
{
|
|
model.PlanOrder = Convert.ToInt32(row["PlanOrder"]);
|
|
}
|
|
if (row["PlanState"] != DBNull.Value)
|
|
{
|
|
model.PlanState = Convert.ToInt32(row["PlanState"]);
|
|
}
|
|
if (row["PlanStartTime"] != DBNull.Value)
|
|
{
|
|
model.PlanStartTime = Convert.ToString(row["PlanStartTime"]);
|
|
}
|
|
if (row["UpAmount"] != DBNull.Value)
|
|
{
|
|
model.UpAmount = Convert.ToInt32(row["UpAmount"]);
|
|
}
|
|
if (row["DownAount"] != DBNull.Value)
|
|
{
|
|
model.DownAount = Convert.ToInt32(row["DownAount"]);
|
|
}
|
|
if (row["Version"] != DBNull.Value)
|
|
{
|
|
model.Version = Convert.ToString(row["Version"]);
|
|
}
|
|
if (row["IsDeleted"] != DBNull.Value)
|
|
{
|
|
model.IsDeleted = row["IsDeleted"].ToString() == "False" ? false : true;
|
|
}
|
|
if (row["CreateTime"] != DBNull.Value)
|
|
{
|
|
model.CreateTime = Convert.ToString(row["CreateTime"]);
|
|
}
|
|
if (row["CreateId"] != DBNull.Value)
|
|
{
|
|
model.CreateId = Convert.ToInt32(row["CreateId"]);
|
|
}
|
|
if (row["CreateBy"] != DBNull.Value)
|
|
{
|
|
model.CreateBy = Convert.ToString(row["CreateBy"]);
|
|
}
|
|
if (row["ModifyTime"] != DBNull.Value)
|
|
{
|
|
model.ModifyTime = Convert.ToString(row["ModifyTime"]);
|
|
}
|
|
if (row["ModifyId"] != DBNull.Value)
|
|
{
|
|
model.ModifyId = Convert.ToInt32(row["ModifyId"]);
|
|
}
|
|
if (row["ModifyBy"] != DBNull.Value)
|
|
{
|
|
model.ModifyBy = Convert.ToString(row["ModifyBy"]);
|
|
}
|
|
Items.Add(model);
|
|
}
|
|
}
|
|
return Items;
|
|
}
|
|
/// <summary>
|
|
/// 得到一个对象实体集合
|
|
/// </summary>
|
|
public List<basedata_orderplanModel> GetList(string strWhere, MySqlParameter[] parameters)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("select");
|
|
strSql.Append(" Id, OrderNo, ProductCode, ProductDesc, Amount, DATE_FORMAT(OrderStartDate,'%Y-%m-%d %H:%i:%s') OrderStartDate, DATE_FORMAT(OrderEndDate,'%Y-%m-%d %H:%i:%s') OrderEndDate, PlanWeek, DATE_FORMAT(PlanDate,'%Y-%m-%d %H:%i:%s') PlanDate, PlanType, PlanOrder, PlanState, DATE_FORMAT(PlanStartTime,'%Y-%m-%d %H:%i:%s') PlanStartTime, UpAmount, DownAount, Version, IsDeleted, DATE_FORMAT(CreateTime,'%Y-%m-%d %H:%i:%s') CreateTime, CreateId, CreateBy, DATE_FORMAT(ModifyTime,'%Y-%m-%d %H:%i:%s') ModifyTime, ModifyId, ModifyBy");
|
|
strSql.Append(" from basedata_orderplan");
|
|
if (!string.IsNullOrWhiteSpace(strWhere))
|
|
strSql.Append(" where " + strWhere);
|
|
List<basedata_orderplanModel> Items = new List<basedata_orderplanModel>();
|
|
DataSet ds = DbHelperSQLServer.Query(strSql.ToString(), parameters);
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
foreach (DataRow row in ds.Tables[0].Rows)
|
|
{
|
|
basedata_orderplanModel model = new basedata_orderplanModel();
|
|
if (row["Id"] != DBNull.Value)
|
|
{
|
|
model.Id = Convert.ToInt32(row["Id"]);
|
|
}
|
|
if (row["OrderNo"] != DBNull.Value)
|
|
{
|
|
model.OrderNo = Convert.ToString(row["OrderNo"]);
|
|
}
|
|
if (row["ProductCode"] != DBNull.Value)
|
|
{
|
|
model.ProductCode = Convert.ToString(row["ProductCode"]);
|
|
}
|
|
if (row["ProductDesc"] != DBNull.Value)
|
|
{
|
|
model.ProductDesc = Convert.ToString(row["ProductDesc"]);
|
|
}
|
|
if (row["Amount"] != DBNull.Value)
|
|
{
|
|
model.Amount = Convert.ToInt32(row["Amount"]);
|
|
}
|
|
if (row["OrderStartDate"] != DBNull.Value)
|
|
{
|
|
model.OrderStartDate = Convert.ToString(row["OrderStartDate"]);
|
|
}
|
|
if (row["OrderEndDate"] != DBNull.Value)
|
|
{
|
|
model.OrderEndDate = Convert.ToString(row["OrderEndDate"]);
|
|
}
|
|
if (row["PlanWeek"] != DBNull.Value)
|
|
{
|
|
model.PlanWeek = Convert.ToInt32(row["PlanWeek"]);
|
|
}
|
|
if (row["PlanDate"] != DBNull.Value)
|
|
{
|
|
model.PlanDate = Convert.ToString(row["PlanDate"]);
|
|
}
|
|
if (row["PlanType"] != DBNull.Value)
|
|
{
|
|
model.PlanType = Convert.ToString(row["PlanType"]);
|
|
}
|
|
if (row["PlanOrder"] != DBNull.Value)
|
|
{
|
|
model.PlanOrder = Convert.ToInt32(row["PlanOrder"]);
|
|
}
|
|
if (row["PlanState"] != DBNull.Value)
|
|
{
|
|
model.PlanState = Convert.ToInt32(row["PlanState"]);
|
|
}
|
|
if (row["PlanStartTime"] != DBNull.Value)
|
|
{
|
|
model.PlanStartTime = Convert.ToString(row["PlanStartTime"]);
|
|
}
|
|
if (row["UpAmount"] != DBNull.Value)
|
|
{
|
|
model.UpAmount = Convert.ToInt32(row["UpAmount"]);
|
|
}
|
|
if (row["DownAount"] != DBNull.Value)
|
|
{
|
|
model.DownAount = Convert.ToInt32(row["DownAount"]);
|
|
}
|
|
if (row["Version"] != DBNull.Value)
|
|
{
|
|
model.Version = Convert.ToString(row["Version"]);
|
|
}
|
|
if (row["IsDeleted"] != DBNull.Value)
|
|
{
|
|
model.IsDeleted = row["IsDeleted"].ToString() == "False" ? false : true;
|
|
}
|
|
if (row["CreateTime"] != DBNull.Value)
|
|
{
|
|
model.CreateTime = Convert.ToString(row["CreateTime"]);
|
|
}
|
|
if (row["CreateId"] != DBNull.Value)
|
|
{
|
|
model.CreateId = Convert.ToInt32(row["CreateId"]);
|
|
}
|
|
if (row["CreateBy"] != DBNull.Value)
|
|
{
|
|
model.CreateBy = Convert.ToString(row["CreateBy"]);
|
|
}
|
|
if (row["ModifyTime"] != DBNull.Value)
|
|
{
|
|
model.ModifyTime = Convert.ToString(row["ModifyTime"]);
|
|
}
|
|
if (row["ModifyId"] != DBNull.Value)
|
|
{
|
|
model.ModifyId = Convert.ToInt32(row["ModifyId"]);
|
|
}
|
|
if (row["ModifyBy"] != DBNull.Value)
|
|
{
|
|
model.ModifyBy = Convert.ToString(row["ModifyBy"]);
|
|
}
|
|
Items.Add(model);
|
|
}
|
|
}
|
|
return Items;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 得到一个对象实体
|
|
/// </summary>
|
|
public vbasedata_orderplanModel GetVModel(int id)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("select");
|
|
strSql.Append(" Id, OrderNo, ProductCode, Amount, DATE_FORMAT(OrderStartDate,'%Y-%m-%d %H:%i:%s') OrderStartDate, DATE_FORMAT(OrderEndDate,'%Y-%m-%d %H:%i:%s') OrderEndDate, PlanWeek, DATE_FORMAT(PlanDate,'%Y-%m-%d %H:%i:%s') PlanDate, PlanType, PlanOrder, PlanState, DATE_FORMAT(PlanStartTime,'%Y-%m-%d %H:%i:%s') PlanStartTime, UpAmount, DownAount, Version, ProductDesc, GateMaterialNo, BoxMaterialNo, GateMaterialNm, BoxMaterialNm");
|
|
strSql.Append(" from vbasedata_orderplan");
|
|
strSql.Append(" where id=@id");
|
|
MySqlParameter[] parameters = {
|
|
new MySqlParameter("@id", MySqlDbType.Int32)
|
|
};
|
|
parameters[0].Value = id;
|
|
vbasedata_orderplanModel model = new vbasedata_orderplanModel();
|
|
DataSet ds = DbHelperSQLServer.Query(strSql.ToString(), parameters);
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
if (ds.Tables[0].Rows[0]["Id"] != DBNull.Value)
|
|
{
|
|
model.Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["OrderNo"] != DBNull.Value)
|
|
{
|
|
model.OrderNo = Convert.ToString(ds.Tables[0].Rows[0]["OrderNo"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["ProductCode"] != DBNull.Value)
|
|
{
|
|
model.ProductCode = Convert.ToString(ds.Tables[0].Rows[0]["ProductCode"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["Amount"] != DBNull.Value)
|
|
{
|
|
model.Amount = Convert.ToInt32(ds.Tables[0].Rows[0]["Amount"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["OrderStartDate"] != DBNull.Value)
|
|
{
|
|
model.OrderStartDate = Convert.ToString(ds.Tables[0].Rows[0]["OrderStartDate"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["OrderEndDate"] != DBNull.Value)
|
|
{
|
|
model.OrderEndDate = Convert.ToString(ds.Tables[0].Rows[0]["OrderEndDate"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["PlanWeek"] != DBNull.Value)
|
|
{
|
|
model.PlanWeek = Convert.ToInt32(ds.Tables[0].Rows[0]["PlanWeek"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["PlanDate"] != DBNull.Value)
|
|
{
|
|
model.PlanDate = Convert.ToString(ds.Tables[0].Rows[0]["PlanDate"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["PlanType"] != DBNull.Value)
|
|
{
|
|
model.PlanType = Convert.ToString(ds.Tables[0].Rows[0]["PlanType"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["PlanOrder"] != DBNull.Value)
|
|
{
|
|
model.PlanOrder = Convert.ToInt32(ds.Tables[0].Rows[0]["PlanOrder"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["PlanState"] != DBNull.Value)
|
|
{
|
|
model.PlanState = Convert.ToInt32(ds.Tables[0].Rows[0]["PlanState"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["PlanStartTime"] != DBNull.Value)
|
|
{
|
|
model.PlanStartTime = Convert.ToString(ds.Tables[0].Rows[0]["PlanStartTime"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["UpAmount"] != DBNull.Value)
|
|
{
|
|
model.UpAmount = Convert.ToInt32(ds.Tables[0].Rows[0]["UpAmount"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["DownAount"] != DBNull.Value)
|
|
{
|
|
model.DownAount = Convert.ToInt32(ds.Tables[0].Rows[0]["DownAount"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["Version"] != DBNull.Value)
|
|
{
|
|
model.Version = Convert.ToString(ds.Tables[0].Rows[0]["Version"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["ProductDesc"] != DBNull.Value)
|
|
{
|
|
model.ProductDesc = Convert.ToString(ds.Tables[0].Rows[0]["ProductDesc"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["GateMaterialNo"] != DBNull.Value)
|
|
{
|
|
model.GateMaterialNo = Convert.ToString(ds.Tables[0].Rows[0]["GateMaterialNo"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["BoxMaterialNo"] != DBNull.Value)
|
|
{
|
|
model.BoxMaterialNo = Convert.ToString(ds.Tables[0].Rows[0]["BoxMaterialNo"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["GateMaterialNm"] != DBNull.Value)
|
|
{
|
|
model.GateMaterialNm = Convert.ToString(ds.Tables[0].Rows[0]["GateMaterialNm"]);
|
|
}
|
|
if (ds.Tables[0].Rows[0]["BoxMaterialNm"] != DBNull.Value)
|
|
{
|
|
model.BoxMaterialNm = Convert.ToString(ds.Tables[0].Rows[0]["BoxMaterialNm"]);
|
|
}
|
|
return model;
|
|
}
|
|
else
|
|
{
|
|
return null;
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 得到一个对象实体集合
|
|
/// </summary>
|
|
public List<vbasedata_orderplanModel> GetVList(string strWhere)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("select");
|
|
strSql.Append(" Id, OrderNo, ProductCode, Amount, DATE_FORMAT(OrderStartDate,'%Y-%m-%d %H:%i:%s') OrderStartDate, DATE_FORMAT(OrderEndDate,'%Y-%m-%d %H:%i:%s') OrderEndDate, PlanWeek, DATE_FORMAT(PlanDate,'%Y-%m-%d %H:%i:%s') PlanDate, PlanType, PlanOrder, PlanState, DATE_FORMAT(PlanStartTime,'%Y-%m-%d %H:%i:%s') PlanStartTime, UpAmount, DownAount, Version, ProductDesc");
|
|
strSql.Append(" from vbasedata_orderplan");
|
|
if (!string.IsNullOrWhiteSpace(strWhere))
|
|
strSql.Append(" where " + strWhere);
|
|
List<vbasedata_orderplanModel> Items = new List<vbasedata_orderplanModel>();
|
|
DataSet ds = DbHelperSQLServer.Query(strSql.ToString());
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
int Index = 1;
|
|
foreach (DataRow row in ds.Tables[0].Rows)
|
|
{
|
|
vbasedata_orderplanModel model = new vbasedata_orderplanModel();
|
|
if (row["Id"] != DBNull.Value)
|
|
{
|
|
model.Id = Convert.ToInt32(row["Id"]);
|
|
}
|
|
if (row["OrderNo"] != DBNull.Value)
|
|
{
|
|
model.OrderNo = Convert.ToString(row["OrderNo"]);
|
|
}
|
|
if (row["ProductCode"] != DBNull.Value)
|
|
{
|
|
model.ProductCode = Convert.ToString(row["ProductCode"]);
|
|
}
|
|
if (row["Amount"] != DBNull.Value)
|
|
{
|
|
model.Amount = Convert.ToInt32(row["Amount"]);
|
|
}
|
|
if (row["OrderStartDate"] != DBNull.Value)
|
|
{
|
|
model.OrderStartDate = Convert.ToString(row["OrderStartDate"]);
|
|
}
|
|
if (row["OrderEndDate"] != DBNull.Value)
|
|
{
|
|
model.OrderEndDate = Convert.ToString(row["OrderEndDate"]);
|
|
}
|
|
if (row["PlanWeek"] != DBNull.Value)
|
|
{
|
|
model.PlanWeek = Convert.ToInt32(row["PlanWeek"]);
|
|
}
|
|
if (row["PlanDate"] != DBNull.Value)
|
|
{
|
|
model.PlanDate = Convert.ToString(row["PlanDate"]);
|
|
}
|
|
if (row["PlanType"] != DBNull.Value)
|
|
{
|
|
model.PlanType = Convert.ToString(row["PlanType"]);
|
|
}
|
|
if (row["PlanOrder"] != DBNull.Value)
|
|
{
|
|
model.PlanOrder = Convert.ToInt32(row["PlanOrder"]);
|
|
}
|
|
if (row["PlanState"] != DBNull.Value)
|
|
{
|
|
model.PlanState = Convert.ToInt32(row["PlanState"]);
|
|
}
|
|
if (row["PlanStartTime"] != DBNull.Value)
|
|
{
|
|
model.PlanStartTime = Convert.ToString(row["PlanStartTime"]);
|
|
}
|
|
if (row["UpAmount"] != DBNull.Value)
|
|
{
|
|
model.UpAmount = Convert.ToInt32(row["UpAmount"]);
|
|
}
|
|
if (row["DownAount"] != DBNull.Value)
|
|
{
|
|
model.DownAount = Convert.ToInt32(row["DownAount"]);
|
|
}
|
|
if (row["Version"] != DBNull.Value)
|
|
{
|
|
model.Version = Convert.ToString(row["Version"]);
|
|
}
|
|
if (row["ProductDesc"] != DBNull.Value)
|
|
{
|
|
model.ProductDesc = Convert.ToString(row["ProductDesc"]);
|
|
}
|
|
if (row["GateMaterialNo"] != DBNull.Value)
|
|
{
|
|
model.GateMaterialNo = Convert.ToString(row["GateMaterialNo"]);
|
|
}
|
|
if (row["BoxMaterialNo"] != DBNull.Value)
|
|
{
|
|
model.BoxMaterialNo = Convert.ToString(row["BoxMaterialNo"]);
|
|
}
|
|
if (row["GateMaterialNm"] != DBNull.Value)
|
|
{
|
|
model.GateMaterialNm = Convert.ToString(row["GateMaterialNm"]);
|
|
}
|
|
if (row["BoxMaterialNm"] != DBNull.Value)
|
|
{
|
|
model.BoxMaterialNm = Convert.ToString(row["BoxMaterialNm"]);
|
|
}
|
|
model.Index = Index;
|
|
Index++;
|
|
Items.Add(model);
|
|
}
|
|
}
|
|
return Items;
|
|
}
|
|
/// <summary>
|
|
/// 得到一个对象实体集合
|
|
/// </summary>
|
|
public List<vbasedata_orderplanModel> GetVList(string strWhere, MySqlParameter[] parameters)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("select");
|
|
strSql.Append(" Id, OrderNo, ProductCode, Amount, DATE_FORMAT(OrderStartDate,'%Y-%m-%d %H:%i:%s') OrderStartDate, DATE_FORMAT(OrderEndDate,'%Y-%m-%d %H:%i:%s') OrderEndDate, PlanWeek, DATE_FORMAT(PlanDate,'%Y-%m-%d %H:%i:%s') PlanDate, PlanType, PlanOrder, PlanState, DATE_FORMAT(PlanStartTime,'%Y-%m-%d %H:%i:%s') PlanStartTime, UpAmount, DownAount, Version, ProductDesc, GateMaterialNo, BoxMaterialNo, GateMaterialNm, BoxMaterialNm");
|
|
strSql.Append(" from vbasedata_orderplan");
|
|
if (!string.IsNullOrWhiteSpace(strWhere))
|
|
strSql.Append(" where " + strWhere);
|
|
List<vbasedata_orderplanModel> Items = new List<vbasedata_orderplanModel>();
|
|
DataSet ds = DbHelperSQLServer.Query(strSql.ToString(), parameters);
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
foreach (DataRow row in ds.Tables[0].Rows)
|
|
{
|
|
vbasedata_orderplanModel model = new vbasedata_orderplanModel();
|
|
if (row["Id"] != DBNull.Value)
|
|
{
|
|
model.Id = Convert.ToInt32(row["Id"]);
|
|
}
|
|
if (row["OrderNo"] != DBNull.Value)
|
|
{
|
|
model.OrderNo = Convert.ToString(row["OrderNo"]);
|
|
}
|
|
if (row["ProductCode"] != DBNull.Value)
|
|
{
|
|
model.ProductCode = Convert.ToString(row["ProductCode"]);
|
|
}
|
|
if (row["Amount"] != DBNull.Value)
|
|
{
|
|
model.Amount = Convert.ToInt32(row["Amount"]);
|
|
}
|
|
if (row["OrderStartDate"] != DBNull.Value)
|
|
{
|
|
model.OrderStartDate = Convert.ToString(row["OrderStartDate"]);
|
|
}
|
|
if (row["OrderEndDate"] != DBNull.Value)
|
|
{
|
|
model.OrderEndDate = Convert.ToString(row["OrderEndDate"]);
|
|
}
|
|
if (row["PlanWeek"] != DBNull.Value)
|
|
{
|
|
model.PlanWeek = Convert.ToInt32(row["PlanWeek"]);
|
|
}
|
|
if (row["PlanDate"] != DBNull.Value)
|
|
{
|
|
model.PlanDate = Convert.ToString(row["PlanDate"]);
|
|
}
|
|
if (row["PlanType"] != DBNull.Value)
|
|
{
|
|
model.PlanType = Convert.ToString(row["PlanType"]);
|
|
}
|
|
if (row["PlanOrder"] != DBNull.Value)
|
|
{
|
|
model.PlanOrder = Convert.ToInt32(row["PlanOrder"]);
|
|
}
|
|
if (row["PlanState"] != DBNull.Value)
|
|
{
|
|
model.PlanState = Convert.ToInt32(row["PlanState"]);
|
|
}
|
|
if (row["PlanStartTime"] != DBNull.Value)
|
|
{
|
|
model.PlanStartTime = Convert.ToString(row["PlanStartTime"]);
|
|
}
|
|
if (row["UpAmount"] != DBNull.Value)
|
|
{
|
|
model.UpAmount = Convert.ToInt32(row["UpAmount"]);
|
|
}
|
|
if (row["DownAount"] != DBNull.Value)
|
|
{
|
|
model.DownAount = Convert.ToInt32(row["DownAount"]);
|
|
}
|
|
if (row["Version"] != DBNull.Value)
|
|
{
|
|
model.Version = Convert.ToString(row["Version"]);
|
|
}
|
|
if (row["ProductDesc"] != DBNull.Value)
|
|
{
|
|
model.ProductDesc = Convert.ToString(row["ProductDesc"]);
|
|
}
|
|
if (row["GateMaterialNo"] != DBNull.Value)
|
|
{
|
|
model.GateMaterialNo = Convert.ToString(row["GateMaterialNo"]);
|
|
}
|
|
if (row["BoxMaterialNo"] != DBNull.Value)
|
|
{
|
|
model.BoxMaterialNo = Convert.ToString(row["BoxMaterialNo"]);
|
|
}
|
|
if (row["GateMaterialNm"] != DBNull.Value)
|
|
{
|
|
model.GateMaterialNm = Convert.ToString(row["GateMaterialNm"]);
|
|
}
|
|
if (row["BoxMaterialNm"] != DBNull.Value)
|
|
{
|
|
model.BoxMaterialNm = Convert.ToString(row["BoxMaterialNm"]);
|
|
}
|
|
Items.Add(model);
|
|
}
|
|
}
|
|
return Items;
|
|
}
|
|
|
|
public DataSet GetData(string strWhere)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
// strSql.Append(@"SELECT DISTINCT
|
|
// basedata_materialinfo.MaterialNo,basedata_materialinfo.MaterialNm,basedata_orderplan.Amount,
|
|
//sum(basedata_carrealinfo.Number) as carnumber,(case when basedata_orderplan.Id>0
|
|
//then 1 else 0 end ) as orderplan
|
|
// FROM
|
|
//basedata_materialinfo
|
|
// inner JOIN basedata_orderplan ON basedata_orderplan.ProductCode = basedata_materialinfo.MaterialNo and
|
|
//basedata_orderplan.PlanWeek=weekofyear(curdate())
|
|
//left join basedata_carrealinfo on basedata_carrealinfo.MaterialNo=basedata_materialinfo.MaterialNo
|
|
//group by basedata_materialinfo.MaterialNo,basedata_materialinfo.MaterialNm,basedata_orderplan.Amount
|
|
//order by basedata_materialinfo.MaterialNm,Amount desc,carnumber desc;
|
|
// ");
|
|
strSql.Append(@"SELECT DISTINCT
|
|
basedata_materialinfo.MaterialNo,basedata_materialinfo.MaterialNm,basedata_orderplan.Amount,
|
|
sum(basedata_carrealinfo.Number) as carnumber
|
|
FROM
|
|
basedata_materialinfo
|
|
inner JOIN basedata_orderplan ON basedata_orderplan.ProductCode = basedata_materialinfo.MaterialNo and
|
|
basedata_orderplan.PlanWeek="+ strWhere + @"
|
|
left join basedata_carrealinfo on basedata_carrealinfo.MaterialNo=basedata_materialinfo.MaterialNo
|
|
group by basedata_materialinfo.MaterialNo,basedata_materialinfo.MaterialNm,basedata_orderplan.Amount
|
|
order by basedata_materialinfo.MaterialNm,Amount desc,carnumber desc;
|
|
");
|
|
|
|
return DbHelperSQLServer.Query(strSql.ToString());
|
|
}
|
|
}
|
|
}
|