/// <summary>
/// 取得布種清單
/// </summary>
/// <returns></returns>
public IEnumerable <Fabric> GetFabricList()
{
string sqlCmd = "SELECT * FROM Fabric";
var result = DapperHelper.QueryCollection <Fabric>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd);
return(result);
}
public int UpdateProcessOrderColorDetailStatusByLastComplete(int orderFlowNo, IEnumerable <int> orderColorDetailNoList)
{
var sqlCmd = @" DECLARE @lastOrderFlowNo INT
SELECT TOP 1 @lastOrderFlowNo = OrderFlowNo FROM ProcessOrderFlowDate
WHERE OrderColorDetailNo = @OrderColorDetailNo
ORDER BY OrderFlowNo DESC
IF(@lastOrderFlowNo = @OrderFlowNo)
begin
UPDATE ProcessOrderColorDetail
SET Status = 5,
UpdateDate = GETDATE()
WHERE OrderColorDetailNo IN @OrderColorDetailNoList
end";
var parameter =
new
{
OrderColorDetailNo = orderColorDetailNoList.First(),
OrderFlowNo = orderFlowNo,
OrderColorDetailNoList = orderColorDetailNoList
};
var count = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter);
return(count);
}
/// <summary>
/// 取得所有紗商的紗價
/// </summary>
/// <returns></returns>
public IEnumerable <MerchantYarnPrice> GetMerchantYarnPriceList()
{
string sqlCmd = @"SELECT * FROM YarnPrice";
var result = DapperHelper.QueryCollection <MerchantYarnPrice>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd);
return(result);
}
/// <summary>
/// 新增加工程序
/// </summary>
/// <param name="processSequenceDetails"></param>
/// <returns></returns>
public List <int> InsertProcessSequence(List <ProcessSequenceDetail> processSequenceDetails)
{
string sqlCmd = @"
INSERT INTO [dbo].[ProcessSequence]
([FabricID]
,[FactoryID]
,[ProcessItem]
,[Loss]
,[WorkPay]
,[Order]
,[Group])
OUTPUT INSERTED.SequenceNo
VALUES
(@FabricID
,@FactoryID
,@ProcessItem
,@Loss
,@WorkPay
,@Order
,@Group)";
List <int> sequenceNoList = new List <int>();
foreach (var item in processSequenceDetails)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@FabricID", SqlDbType.Int)
{
Value = item.FabricID
},
new SqlParameter("@FactoryID", SqlDbType.Int)
{
Value = item.FactoryID
},
new SqlParameter("@ProcessItem", SqlDbType.TinyInt)
{
Value = item.ProcessItem
},
new SqlParameter("@Loss", SqlDbType.Decimal)
{
Value = item.Loss
},
new SqlParameter("@WorkPay", SqlDbType.SmallInt)
{
Value = item.WorkPay
},
new SqlParameter("@Order", SqlDbType.TinyInt)
{
Value = item.Order
},
new SqlParameter("@Group", SqlDbType.TinyInt)
{
Value = item.Group
}
};
sequenceNoList.Add(DapperHelper.Query <int>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameters));
}
return(sequenceNoList);
}
public int UpdateProcessOrderFlowDate(List <ProcessOrderFlowFactoryName> processOrderFlow)
{
string sql = @"UPDATE ProcessOrderFlow SET InputDate = @InputDate,CompleteDate = @CompleteDate WHERE OrderDetailNo = @OrderDetailNo;";
var count = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sql, processOrderFlow);
return(count);
}
/// <summary>
/// 修改加工程序,可修改工繳,損耗,順序
/// </summary>
/// <param name="sequenceNo"></param>
/// <param name="loss"></param>
/// <param name="workPay"></param>
/// <param name="order"></param>
/// <returns></returns>
public int EditProcessSequence(int sequenceNo, decimal loss, int workPay, int order)
{
var sqlCmd = @"UPDATE [dbo].[ProcessSequence]
SET
[Loss] = @Loss
,[WorkPay] = @WorkPay
,[Order] = @Order
WHERE SequenceNo=@SequenceNo";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@SequenceNo", SqlDbType.Int)
{
Value = sequenceNo
},
new SqlParameter("@Loss", SqlDbType.Decimal)
{
Value = loss
},
new SqlParameter("@WorkPay", SqlDbType.Int)
{
Value = workPay
},
new SqlParameter("@Order", SqlDbType.Int)
{
Value = order
}
};
var result = DapperHelper.ExecuteParameter(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameters);
return(result);
}
/// <summary>
/// 更新客戶布種單價
/// </summary>
/// <returns></returns>
public int UpdateCustomerTextilePrice(CustomerCheckBillSheet selectedCustomerCheckBillSheet, int updateCustomerPrice)
{
string sql = @"UPDATE dbo.CustomerTextilePrice
SET Price = @Price
WHERE AccountCustomerID = @AccountCustomerID AND AccountTextileID = @AccountTextileID";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@AccountCustomerID", SqlDbType.NChar)
{
Value = selectedCustomerCheckBillSheet.C_01
},
new SqlParameter("@AccountTextileID", SqlDbType.Int)
{
Value = selectedCustomerCheckBillSheet.AccountTextileID
},
new SqlParameter("@Price", SqlDbType.Int)
{
Value = updateCustomerPrice
}
};
var result = DapperHelper.ExecuteParameter(AppSettingConfig.ConnectionString(), CommandType.Text, sql, parameters);
return(result);
}
/// <summary>
/// 取得所有布種預設單價
/// </summary>
/// <returns></returns>
public IEnumerable <AccountTextile> GetAccountTextile()
{
string sql = @"SELECT * FROM AccountTextile;";
var result = DapperHelper.QueryCollection <AccountTextile>(AppSettingConfig.ConnectionString(), CommandType.Text, sql);
return(result);
}
/// <summary>
/// 取得客戶名稱清單
/// </summary>
/// <returns></returns>
public IEnumerable <CustomerName> GetCustomerNameList()
{
var sqlCmd = "SELECT Name FROM Customer";
var result = DapperHelper.QueryCollection <CustomerName>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd);
return(result);
}
/// <summary>
/// 新增紗規格
/// </summary>
/// <param name="yarnSpecification"></param>
/// <returns></returns>
public int AddYarnSpecification(YarnSpecification yarnSpecification)
{
string sqlCmd = @"INSERT INTO [dbo].[YarnSpecification]
([Ingredient],[YarnCount],[Color])
VALUES
(@Ingredient,@YarnCount,@Color)";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Ingredient", SqlDbType.NVarChar)
{
Value = yarnSpecification.Ingredient
},
new SqlParameter("@YarnCount", SqlDbType.NVarChar)
{
Value = yarnSpecification.YarnCount
},
new SqlParameter("@Color", SqlDbType.NVarChar)
{
Value = yarnSpecification.Color
}
};
var result = DapperHelper.ExecuteParameter(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameters);
return(result);
}
public int NewProcessOrderFlow(ProcessOrderFlow processOrderFlow, IEnumerable <int> orderColorDetailNo)
{
var sqlCmd = @"INSERT INTO ProcessOrderFlow
OUTPUT inserted.OrderDetailNo
VALUES
(@OrderNo,@FactoryID);";
SqlParameter[] parameter = new SqlParameter[]
{
new SqlParameter("@OrderNo", SqlDbType.Int)
{
Value = processOrderFlow.OrderNo
},
new SqlParameter("@FactoryID", SqlDbType.Int)
{
Value = processOrderFlow.FactoryID
}
};
var orderDetailNo = DapperHelper.Query <int>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter);
var cmd = @"INSERT INTO ProcessOrderFlowDate
VALUES(@OrderColorDetailNo,@OrderDetailNo,null,null,GETDATE());";
var parameters =
orderColorDetailNo.Select(s => new { OrderColorDetailNo = s, OrderDetailNo = orderDetailNo, });
var count = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, cmd, parameters);
return(count);
}
public int InsertProcessOrderFlowDate(List <ProcessOrderFlowDate> proecessOrderFlowDateList)
{
string sql = @"INSERT INTO ProcessOrderFlowDate
VALUES
(@OrderColorDetailNo,@OrderFlowNo,@InputDate,@CompleteDate,@UpdateDate);";
return(DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sql, proecessOrderFlowDateList));
}
/// <summary>
/// 依據多個加工訂單顏色明細編號號取得工廠直送清單
/// </summary>
/// <param name="processOrderColorDetailNo"></param>
/// <returns></returns>
public IEnumerable <FactoryShippingName> GetFactoryShippingNameList(IEnumerable <int> processOrderColorDetailNo)
{
var sqlCmd = @"select fs.ShippingNo,fs.OrderColorDetailNo,c.Name,fs.Quantity,fs.CreateDate,fs.ShippingDate from FactoryShipping fs
inner join Customer c on c.CustomerID = fs.CustomerID
where OrderColorDetailNo IN @OrderColorDetailNo";
var parameter = (new { OrderColorDetailNo = processOrderColorDetailNo });
return(DapperHelper.QueryCollection <FactoryShippingName, object>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter));
}
public int InsertProcessOrderFlow(List <ProcessOrderFlow> processOrderFlow)
{
string sql = @"INSERT INTO ProcessOrderFlow
VALUES
(@OrderNo,@FactoryID);";
var result = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sql, processOrderFlow);
return(result);
}
/// <summary>
/// 新增工廠清單
/// </summary>
/// <param name="factory"></param>
/// <returns></returns>
public int InsertFactory(Factory factory)
{
var sqlCmd = @"INSERT INTO [dbo].[Factory]
VALUES
(@Name,@PhoneNumber,@CellPhone,@Fax,@Process,@Address,@Sort)";
var result = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, factory);
return(result);
}
/// <summary>
/// 取得工廠編號
/// </summary>
/// <param name="FactoryName"></param>
/// <returns></returns>
public IEnumerable <FactoryIdentity> GetFactoryIdentiys(string[] FactoryNameString)
{
var sqlCmd = "SELECT FactoryID,Name FROM Factory WHERE Name IN @Name";
var parameter = (new { Name = FactoryNameString });
//var y = (new { Name = new[] { "強金利", "祥玉" } });
var result = DapperHelper.QueryCollection <FactoryIdentity, object>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter);
return(result);
}
/// <summary>
/// 新增布種
/// </summary>
/// <param name="fabric"></param>
/// <returns></returns>
public int AddFabric(Fabric fabric)
{
var sqlCmd = @"INSERT INTO [dbo].[Fabric]
VALUES
(@FabricName,@AverageUnitPrice,@AverageCost,@UpdateDate)";
var result = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, fabric);
return(result);
}
/// <summary>
/// 新增布種顏色的成分比例
/// </summary>
/// <param name="colorNo"></param>
/// <param name="list"></param>
/// <returns></returns>
public int InsertFabricIngredientProportions(IEnumerable <FabricProportion> fabricProportions)
{
var sqlCmd = @"INSERT INTO [dbo].[FabricProportion]
([ColorNo],[YarnPriceNo],[Proportion],[Group],[CreateDate])
VALUES
(@ColorNo,@YarnPriceNo,@Proportion,@Group,@CreateDate)";
var result = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, fabricProportions);
return(result);
}
/// <summary>
/// 新增布種顏色
/// </summary>
/// <param name="fabricColors"></param>
public int AddFabricColorList(List <FabricColor> fabricColors)
{
string sqlCmd = @"INSERT INTO FabricColor
(FabricID,Color)
VALUES
(@FabricID,@Color);";
int count = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, fabricColors);
return(count);
}
/// <summary>
/// 依據狀態取得加工訂單
/// </summary>
/// <param name="Status"></param>
/// <returns></returns>
public IEnumerable <ProcessOrder> GetProcessOrderByStatus(List <ProcessOrderColorStatus> status)
{
var sqlCmd = @"select distinct po.OrderNo,po.OrderString,po.Fabric,po.Specification,po.ProcessItem,po.Precautions,po.Memo,po.HandFeel,po.CreateDate from ProcessOrder po
left join ProcessOrderColorDetail pocd on po.OrderNo = pocd.OrderNo
where Status in @Status";
var parameter = (new { Status = status });
var result = DapperHelper.QueryCollection <ProcessOrder, object>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter);
return(result);
}
/// <summary>
/// 新增加工程序顏色對照
/// </summary>
/// <param name="processSequenceColorMapping"></param>
/// <returns></returns>
public int InsertProcessSequenceColorMapping(IEnumerable <ProcessSequenceColorMapping> processSequenceColorMapping)
{
string sqlCmd = @"
INSERT INTO [dbo].[ProcessSequenceColorMapping]
([ColorNo],[SequenceNo])
VALUES
(@ColorNo,@SequenceNo)";
int count = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, processSequenceColorMapping);
return(count);
}
/// <summary>
/// 更新布種比例成分
/// </summary>
/// <param name="fabricIngredientProportions"></param>
/// <returns></returns>
public int UpdateFabricProportion(List <FabricIngredientProportion> fabricIngredientProportions)
{
var sqlCmd = @"UPDATE [dbo].[FabricProportion]
SET [YarnPriceNo] = @YarnPriceNo
,[Proportion] = @Proportion
,[UpdateDate] = GETDATE()
WHERE ProportionNo=@ProportionNo";
var result = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, fabricIngredientProportions);
return(result);
}
/// <summary>
/// 以布種編號取得布種顏色
/// </summary>
/// <param name="fabricIDList"></param>
/// <returns></returns>
public IEnumerable <FabricColor> GetFabricColorListByFabricID(IEnumerable <int> fabricIDList)
{
string sqlCmd = @"SELECT * FROM FabricColor WHERE FabricID IN @FabricID";
var parameter =
new
{
FabricID = fabricIDList
};
var result = DapperHelper.QueryCollection <FabricColor, object>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter);
return(result);
}
/// <summary>
/// 取得紗規格清單
/// </summary>
/// <returns></returns>
public IEnumerable <YarnSpecification> GetYarnSpecificationList()
{
string sqlCmd = @"SELECT [YarnSpecificationNo]
,[Ingredient]
,[YarnCount]
,[Color]
,[CreateDate]
FROM [YarnSpecification]";
var result = DapperHelper.QueryCollection <YarnSpecification>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd);
return(result);
}
/// <summary>
/// 更新布種
/// </summary>
/// <param name="fabric"></param>
/// <returns></returns>
public int EditFabric(Fabric fabric)
{
var sqlCmd = @"UPDATE [dbo].[Fabric]
SET [FabricName] = @FabricName
,[AverageUnitPrice] = @AverageUnitPrice
,[AverageCost] = @AverageCost
,[UpdateDate] = @UpdateDate
WHERE FabricID=@FabricID";
var result = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, fabric);
return(result);
}
/// <summary>
/// 取得加工訂單狀態
/// </summary>
/// <returns></returns>
public IEnumerable <ProcessOrderStatus> GetProcessOrderStatus()
{
string sql = @"
SELECT PO.OrderNo,PO.OrderString,PO.Fabric,POCD.Color,POCD.Quantity,POCD.Status,POCD.UpdateDate,F.Name AS FactoryName,POF.OrderDetailNo FROM ProcessOrderColorDetail POCD
INNER JOIN ProcessOrder PO ON PO.OrderNo = POCD.OrderNo
INNER JOIN ProcessOrderFlowDate POFD ON POFD.OrderColorDetailNo = POCD.OrderColorDetailNo
INNER JOIN ProcessOrderFlow POF ON POF.OrderDetailNo = POFD.OrderFlowNo
INNER JOIN Factory F ON F.FactoryID = POF.FactoryID
WHERE POCD.Status = 5 OR POCD.Status = 6 OR Status = 7 OR Status = 8 OR Status = 9";
return(DapperHelper.QueryCollection <ProcessOrderStatus>(AppSettingConfig.ConnectionString(), CommandType.Text, sql));
}
/// <summary>
/// 以布種顏色編號取得顏色比例
/// </summary>
/// <param name="fabricColorNoList"></param>
/// <returns></returns>
public IEnumerable <FabricProportion> GetFabricProportionByColorNo(List <int> fabricColorNoList)
{
string sqlCmd = @"SELECT * FROM FabricProportion WHERE ColorNo IN @ColorNo";
var parameter =
new
{
ColorNo = fabricColorNoList
};
var result = DapperHelper.QueryCollection <FabricProportion, object>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter);
return(result);
}
/// <summary>
/// 修改工廠明細
/// </summary>
/// <returns></returns>
public int UpdateFactory(Factory factory)
{
var sqlCmd = @"UPDATE [dbo].[Factory]
SET [PhoneNumber] = @PhoneNumber
,[CellPhone] = @CellPhone
,[Fax] = @Fax
,[Address] = @Address
,[Sort] = @Sort
WHERE FactoryID=@FactoryID";
var result = DapperHelper.Execute(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, factory);
return(result);
}
public IEnumerable <ProcessOrderColorDetail> GetProcessOrderColorDetailByOrderNo(int orderNo)
{
var sqlCmd = "select * from ProcessOrderColorDetail WHERE OrderNo = @OrderNo";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@OrderNo", SqlDbType.Int)
{
Value = orderNo
}
};
return(DapperHelper.QueryCollection <ProcessOrderColorDetail>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameters));
}
/// </summary>
/// 取得新增或修改的加工訂單明細
/// </summary>
/// <param name="dateTime"></param>
/// <returns></returns>
public IEnumerable <ProcessOrder> GetNewOrEditProcessOrder(DateTime dateTime, IEnumerable <int> orderNo)
{
var sqlCmd = @"SELECT DISTINCT PO.* FROM ProcessOrder PO
LEFT JOIN ProcessOrderColorDetail POCD ON POCD.OrderNo = PO.OrderNo
LEFT JOIN ProcessOrderFlowDate POFD ON POFD.OrderColorDetailNo = POCD.OrderColorDetailNo
LEFT JOIN FactoryShipping FS ON FS.OrderColorDetailNo = POCD.OrderColorDetailNo
WHERE PO.CreateDate > @DATE OR POCD.UpdateDate > @DATE OR POFD.UpdateDate > @DATE OR FS.CreateDate > @DATE OR PO.OrderNo IN @OrderNo";
var parameter = (new { DATE = dateTime, OrderNo = orderNo });
var result = DapperHelper.QueryCollection <ProcessOrder, object>(AppSettingConfig.ConnectionString(), CommandType.Text, sqlCmd, parameter);
return(result);
}