public DataTable Get查询台账(string Where)
{
DataTable dt = null;
try
{
using (SQLServer db = new SQLServer(SqlStr.ConnStringBuilder.ConnectionString))
{
dt = db.GetDataSetBySQL("select code as 单号,materialCODE as 货物代码,materialNAME as 货物名称,SPECS as 规格,UNITNAME as 单位,QUANTITY as 数量,definestr1 as 颜色,definestr2,definestr3,definestr4,definestr5,definestr6 as 品牌,batchnumber as 批号 from (select code ,materialCODE,materialNAME,SPECS,UNITNAME,QUANTITY,definestr1,definestr2,definestr3,definestr4,definestr5,definestr6,batchnumber from RukuKu a union all select code ,materialCODE,materialNAME,SPECS,UNITNAME,-QUANTITY,definestr1,definestr2,definestr3,definestr4,definestr5,definestr6,batchnumber from cukuKu b union all select code ,materialCODE,materialNAME,SPECS,UNITNAME,-QUANTITY,definestr1,definestr2,definestr3,definestr4,definestr5,definestr6,batchnumber from CheckStockku f ) c where 1=1 and left(code,1)<>'*'" + Where, "noname").Tables[0];
db.Dispose();
}
}
catch (Exception ex)
{
}
return(dt);
}
public DataTable Get查询(string Where)
{
DataTable dt = null;
try
{
using (SQLServer db = new SQLServer(SqlStr.ConnStringBuilder.ConnectionString))
{
dt = db.GetDataSetBySQL("select code as 货物代码,storagename as 仓库,(select materialname from dangdangpuku where code=a.code) as 货物名称 ,(select specs from dangdangpuku where code=a.code) as 规格描述 ,convert(real, quantity) as 数量,zf1 as 颜色,zf2 as 品牌,(select ZF22 from dangdangpuku where code=a.code) AS 粘度,(select ZF23 from dangdangpuku where code=a.code) AS 板厚,(select ZF25 from dangdangpuku where code=a.code) AS 长度,(select ZF24 from dangdangpuku where code=a.code) AS 宽度,batchnumber as 批号 from dangdangkuQtyby物料 a where 1=1 " + Where, "noname").Tables[0];
db.Dispose();
}
}
catch (Exception ex)
{
}
return(dt);
}
public ArrayList Get查询仓库权限(string storagenamespell)
{
ArrayList dt = null;
try
{
using (SQLServer db = new SQLServer(SqlStr.ConnStringBuilder.ConnectionString))
{
dt = db.GetArraylistBySQL("select name from ORaiKu where 1=1 and storagenamespell not like '%" + storagenamespell + "%' ");
db.Dispose();
}
}
catch (Exception ex)
{
}
return(dt);
}
public DataTable Get查询(string Where, string 开始时间, string 结束时间)
{
DataTable dt = null;
try
{
using (SQLServer db = new SQLServer(SqlStr.ConnStringBuilder.ConnectionString))
{
dt = db.GetDataSetBySQL("exec 批次明细查询 '" + 开始时间 + "','" + 结束时间 + "','" + Where + "' ", "noname").Tables[0];
db.Dispose();
}
}
catch (Exception ex)
{
}
return(dt);
}
//生成要显示出来的字段清单 GridVisible='1'
public string SqlField(string biaopu)
{
System.Data.SqlClient.SqlDataReader Dr = null;
using (SQLServer db = new SQLServer(SqlStr.ConnStringBuilder.ConnectionString))
{
Dr = db.GetDataReaderBySQL("select * from qianbuNiangbiao where biaopu='" + biaopu + "' order by Gridorder");
string SqlFieldStr = "id ";
while (Dr.Read())
{
if (Information.IsDBNull(Dr["GridVisible"]))
{
continue;
}
if (Dr["GridVisible"].ToString() == "1")
{
SqlFieldStr += " , " + Dr["ziduanpu"].ToString() + " as " + Dr["ziduandisplay"].ToString() + " ";
}
}
return(SqlFieldStr);
db.Dispose();
}
}
public bool LoadFromSqlServer(string TargetCostSheetID, string NetworkID, string TargetWorkOrderID, int PreviousCostSheet, int Archive)
{
CostSheetID = TargetCostSheetID;
SQLServer SQLServer = new SQLServer();
SqlCommand Command = new SqlCommand();
DataSet dsCostSheet;
DataTable dtTarget;
string sCommandText;
bool bResult = true;
ForecastCostSheetDetail forecastCostSheetDetail = null;
ForecastCostSheetDiscipline forecastCostSheetDiscipline = null;
Part part = null;
try
{
SQLServer.ConnectionString = ConnectionString;
sCommandText = "Reporting.proc_ForecastCostSheet";
Command.CommandText = sCommandText;
Command.Parameters.Add(new SqlParameter("@CostSheetID", CostSheetID));
Command.Parameters.Add(new SqlParameter("@NetworkID", NetworkID));
Command.Parameters.Add(new SqlParameter("@WorkOrderID", TargetWorkOrderID));
Command.Parameters.Add(new SqlParameter("@Previous", PreviousCostSheet));
Command.Parameters.Add(new SqlParameter("@Archive", Archive));
dsCostSheet = SQLServer.GetStoredProcedureDataSet(Command, sCommandText);
dtTarget = dsCostSheet.Tables[0];
if (dtTarget.Rows != null)
{
AddedOn = Convert.ToDateTime(dtTarget.Rows[0]["AddedOn"]);
TargetDate = Convert.ToDateTime(dtTarget.Rows[0]["TargetDate"]);
EndDate = Convert.ToDateTime(dtTarget.Rows[0]["EndDate"]);
AddedBy = dtTarget.Rows[0]["AddedBy"].ToString();
QuoteID = dtTarget.Rows[0]["QuoteID"].ToString();
WorkOrderID = dtTarget.Rows[0]["WorkOrderID"].ToString();
ProjectManager = dtTarget.Rows[0]["ProjectManager"].ToString();
JobStatus = Convert.ToInt32(dtTarget.Rows[0]["Status"]);
ActualHours = Convert.ToDecimal(dtTarget.Rows[0]["ActualHours"]);
Adjusted = Convert.ToBoolean(dtTarget.Rows[0]["Adjusted"]);
}
dtTarget = dsCostSheet.Tables[1];
if (dtTarget.Rows != null)
{
forecastCostSheetDetails = new List <ForecastCostSheetDetail> {
};
foreach (DataRow row in dtTarget.Rows)
{
forecastCostSheetDetail = new ForecastCostSheetDetail();
forecastCostSheetDetail.CostSheetID = row["CostSheetID"].ToString();
forecastCostSheetDetail.CostSheetDetailID = row["CostSheetDetailID"].ToString();
forecastCostSheetDetail.Description = row["Description"].ToString();
forecastCostSheetDetail.DisciplineID = Convert.ToInt16(row["DisciplineID"]);
forecastCostSheetDetail.UnitCost = Convert.ToDecimal(row["UnitCost"]);
forecastCostSheetDetail.Quantity = Convert.ToDecimal(row["Quantity"]);
forecastCostSheetDetail.UnitPrice = Convert.ToDecimal(row["UnitPrice"]);
forecastCostSheetDetail.ReasonForCall = row["ReasonForCall"].ToString();
forecastCostSheetDetail.Calculate();
forecastCostSheetDetails.Add(forecastCostSheetDetail);
}
}
dtTarget = dsCostSheet.Tables[2];
if (dtTarget.Rows != null)
{
Parts = new List <Part> {
};
foreach (DataRow row in dtTarget.Rows)
{
part = new Part();
part.CostSheetID = row["CostSheetID"].ToString();
part.CostSheetDetailID = row["CostSheetDetailID"].ToString();
part.DisciplineID = Convert.ToInt32(row["DisciplineID"]);
part.Description = row["Description"].ToString();
part.PartID = row["PartID"].ToString();
part.Quantity = Convert.ToDecimal(row["Quantity"]);
part.UnitCost = Convert.ToDecimal(row["UnitCost"]);
part.UnitPrice = Convert.ToDecimal(row["UnitPrice"]);
part.ApplyPST = Convert.ToBoolean(row["IncludePST"]);
part.Calculate();
Parts.Add(part);
}
}
dtTarget = dsCostSheet.Tables[3];
if (dtTarget.Rows != null)
{
forecastCostSheetDisciplines = new List <ForecastCostSheetDiscipline> {
};
foreach (DataRow row in dtTarget.Rows)
{
forecastCostSheetDiscipline = new ForecastCostSheetDiscipline();
forecastCostSheetDiscipline.CostSheetID = row["CostSheetID"].ToString();
forecastCostSheetDiscipline.CostSheetDisciplineID = row["CostSheetDisciplineID"].ToString();
forecastCostSheetDiscipline.QuoteID = row["QuoteID"].ToString();
forecastCostSheetDiscipline.DisciplineID = Convert.ToInt16(row["DisciplineID"]);
forecastCostSheetDiscipline.Discipline = row["Discipline"].ToString();
forecastCostSheetDiscipline.Regular = Convert.ToDecimal(row["Hours"]);
forecastCostSheetDiscipline.OT = Convert.ToDecimal(row["HoursOT"]);
forecastCostSheetDiscipline.DT = Convert.ToDecimal(row["HoursDT"]);
forecastCostSheetDiscipline.HourlyCost = Convert.ToDecimal(row["CostRate"]);
forecastCostSheetDiscipline.HourlyRate = Convert.ToDecimal(row["HourlyRate"]);
forecastCostSheetDiscipline.Calculate();
forecastCostSheetDisciplines.Add(forecastCostSheetDiscipline);
}
}
Calculate();
CalculateMonths();
}
catch (Exception ex)
{
bResult = false;
Console.WriteLine(ex.Message);
}
finally
{
SQLServer.Dispose();
}
return(bResult);
}