//var sqlCmd = @"update ProcessOrderFlowDate
// set InputDate = @InputDate,
// UpdateDate = GETDATE()
// where OrderFlowNo = @OrderFlowNo and OrderColorDetailNo in @OrderColorDetailNo";
//var parameter =
// new
// {
// OrderColorDetailNo = orderColorDetailNoList,
// InputDate = date,
// OrderFlowNo = orderFlowNo
// };
public void UpdateProductName(string v)
{
string sqlCmd = @"SELECT * from ITEM.dbf WHERE I_03 Like '%*%'";//@"UPDATE ITEM.dbf SET I_03=REPLACE(I_03,'*','X') WHERE I_03 LIKE '%*%'";
var result = DapperHelper.QueryDbfCollection <TrashItem>(AppSettingConfig.DbfConnectionString(), CommandType.Text, sqlCmd);
var replace = result.Select(s => new TrashItem {
I_03 = s.I_03.Replace("*", "X"), F_01 = s.F_01, I_01 = s.I_01
});
foreach (var item in replace)
{
string sqlCmd2 = @"UPDATE ITEM.dbf SET I_03=@I_03 WHERE F_01=@F_01 AND I_01=@I_01";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@I_03", SqlDbType.NVarChar)
{
Value = item.I_03
},
new SqlParameter("@F_01", SqlDbType.NVarChar)
{
Value = item.F_01
},
new SqlParameter("@I_01", SqlDbType.NVarChar)
{
Value = item.I_01
},
};
var count = DapperHelper.QueryDbfCollection <int>(AppSettingConfig.DbfConnectionString(), CommandType.Text, sqlCmd2, parameters);
}
}
public IEnumerable <TrashItem> GetTrashItems()
{
var sqlCmd = "SELECT * FROM ITEM.dbf";
var result = DapperHelper.QueryDbfCollection <TrashItem>(AppSettingConfig.DbfConnectionString(), CommandType.Text, sqlCmd);
return(result);
}
/// <summary>
/// 取得客戶清單
/// </summary>
public IEnumerable <TrashCustomer> GetCustomerList()
{
var sqlCmd = @"SELECT CARD_NO,C_NAME FROM CUST.dbf";
var result = DapperHelper.QueryDbfCollection <TrashCustomer>(AppSettingConfig.DbfConnectionString(), CommandType.Text, sqlCmd);
return(result);
}
/// <summary>
/// 取得客戶出貨紀錄
/// </summary>
/// <param name="customerName"></param>
/// <param name="customerDatePickerBegin"></param>
/// <param name="customerDatePickerEnd"></param>
/// <returns></returns>
public IEnumerable <TrashCustomerShipped> GetCustomerShippedList(string customerName, DateTime customerDatePickerBegin, DateTime customerDatePickerEnd)
{
string sqlCmd = "";
if (string.IsNullOrEmpty(customerName))
{
sqlCmd = string.Concat(@"SELECT DISTINCT cust.C_Name,invosub.IN_DATE,invosub.I_01,item.I_03,invosub.QUANTITY FROM (CUST.dbf AS cust
INNER JOIN INVOSUB.dbf AS invosub ON cust.CARD_NO = invosub.C_01)
INNER JOIN ITEM.dbf AS item ON invosub.I_01 = ITEM.I_01 AND invosub.F_01 = ITEM.F_01
WHERE invosub.IN_DATE Between cDate('" + customerDatePickerBegin.ToString() + "') and cDate('" + customerDatePickerEnd.ToString() + "')");
}
else
{
sqlCmd = @"SELECT DISTINCT cust.C_Name,invosub.IN_DATE,invosub.I_01,item.I_03,invosub.QUANTITY FROM (CUST.dbf AS cust
INNER JOIN INVOSUB.dbf AS invosub ON cust.CARD_NO = invosub.C_01)
INNER JOIN ITEM.dbf AS item ON invosub.I_01 = ITEM.I_01 AND invosub.F_01 = ITEM.F_01
WHERE cust.C_NAME = '" + customerName + "' AND invosub.IN_DATE Between cDate('" + customerDatePickerBegin.ToString() + "') and cDate('" + customerDatePickerEnd.ToString() + "')";
}
//SqlParameter[] parameters = new SqlParameter[]
//{
// new SqlParameter("@CustomerName", SqlDbType.NVarChar) { Value = customerName },
//};
var result = DapperHelper.QueryDbfCollection <TrashCustomerShipped>(AppSettingConfig.DbfConnectionString(), CommandType.Text, sqlCmd);
return(result);
}
public IEnumerable <TrashShipped> GetTrashShippedList(DateTime datePickerBegin, DateTime datePickerEnd)
{
string sqlCmd = "SELECT invosub.IN_DATE,invosub.I_01,item.I_03,invosub.QUANTITY FROM INVOSUB.dbf invosub " +
"INNER JOIN ITEM.dbf item ON invosub.I_01 = item.I_01 AND invosub.F_01 = item.F_01 " +
"WHERE invosub.IN_DATE Between cDate('" + datePickerBegin.ToString() + "') and cDate('" + datePickerEnd.ToString() + "') " +
"ORDER BY invosub.IN_DATE,invosub.I_01";
var result = DapperHelper.QueryDbfCollection <TrashShipped>(AppSettingConfig.DbfConnectionString(), CommandType.Text, sqlCmd);
return(result);
}
/// <summary>
/// 取得客戶出貨紀錄
/// </summary>
/// <param name="CheckBillDate"></param>
/// <returns></returns>
public IEnumerable <TrashCustomerShipped> GetInvoSub(DateTime CheckBillDate)
{
var sqlCmd = @"SELECT INSub.IN_DATE,INSub.Quantity,INSub.Price,INSub.C_01,INSub.F_01,INSub.I_01,INSub.IN_NO,I.I_03,CU.C_Name,INSub.Price,INSub.Time FROM (INVOSUB.dbf AS INSub
INNER JOIN CUST.dbf AS CU ON CU.CARD_NO = INSub.C_01)
INNER JOIN ITEM.dbf AS I ON I.F_01 = INSub.F_01 AND I.I_01 = INSub.I_01
WHERE Year(INSub.IN_DATE) = " + CheckBillDate.Year + " AND Month(INSub.IN_DATE) = " + CheckBillDate.Month;
var result = DapperHelper.QueryDbfCollection <TrashCustomerShipped>(AppSettingConfig.DbfConnectionString(), CommandType.Text, sqlCmd);
return(result);
}
public IEnumerable <TrashItem> GetTrashItemsByFeature(string feature)
{
var sqlCmd = "SELECT * FROM ITEM.dbf WHERE I_03 like @I_03";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@I_03", SqlDbType.NVarChar)
{
Value = feature
}
};
var result = DapperHelper.QueryDbfCollection <TrashItem>(AppSettingConfig.DbfConnectionString(), CommandType.Text, sqlCmd, parameters);
return(result);
}
/// <summary>
/// 以布種名稱取得客戶出貨紀錄
/// </summary>
/// <param name="textileName"></param>
/// <returns></returns>
public IEnumerable <TrashCustomerShipped> GetCustomerShippedListByTextileName(string textileName, DateTime datePickerBegin, DateTime datePickerEnd)
{
var sqlCmd = @"SELECT INSub.IN_DATE,INSub.Quantity,INSub.Price,CU.C_Name,INSub.Price FROM (INVOSUB.dbf AS INSub
INNER JOIN CUST.dbf AS CU ON CU.CARD_NO = INSub.C_01)
INNER JOIN ITEM.dbf AS I ON I.F_01 = INSub.F_01 AND I.I_01 = INSub.I_01
WHERE I.I_03 = @I_03 AND INSub.IN_DATE Between cDate('" + datePickerBegin.ToString() + "') and cDate('" + datePickerEnd.ToString() + "')";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@I_03", SqlDbType.NVarChar)
{
Value = textileName
}
};
var result = DapperHelper.QueryDbfCollection <TrashCustomerShipped>(AppSettingConfig.DbfConnectionString(), CommandType.Text, sqlCmd, parameters);
return(result);
}
/// <summary>
/// 以布種取得客戶出貨紀錄
/// </summary>
/// <param name="trashItem"></param>
/// <returns></returns>
public IEnumerable <TrashCustomerShipped> GetCustomerShippedListByFeature(TrashItem trashItem)
{
var sqlCmd = @"SELECT INSub.IN_DATE,INSub.Quantity,INSub.Price,CU.C_Name,INSub.Price FROM INVOSUB.dbf AS INSub
INNER JOIN CUST.dbf AS CU ON CU.CARD_NO = INSub.C_01
WHERE INSub.F_01 = @F_01 AND INSub.I_01 = @I_01";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@F_01", SqlDbType.NVarChar)
{
Value = trashItem.F_01
},
new SqlParameter("@I_01", SqlDbType.NVarChar)
{
Value = trashItem.I_01
}
};
var result = DapperHelper.QueryDbfCollection <TrashCustomerShipped>(AppSettingConfig.DbfConnectionString(), CommandType.Text, sqlCmd, parameters);
return(result);
}
/// <summary>
/// 更新帳務系統單價
/// </summary>
/// <param name="customerCheckBillSheet"></param>
/// <param name="newPrice"></param>
/// <param name="CheckBillDate"></param>
/// <returns></returns>
public int UpdateInvoSubPrice(CustomerCheckBillSheet customerCheckBillSheet, int newPrice, DateTime CheckBillDate)
{
string sqlCmd2 = @"UPDATE INVOSUB.dbf SET Price=@Price
WHERE C_01 = @C_01 AND I_01 = @I_01 AND F_01 = @F_01 AND Month(IN_DATE) = @IN_DATE1 AND Year(IN_DATE) = @IN_DATE2";
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Price", SqlDbType.Int)
{
Value = newPrice
},
new SqlParameter("@C_01", SqlDbType.NVarChar)
{
Value = customerCheckBillSheet.C_01
},
new SqlParameter("@I_01", SqlDbType.NVarChar)
{
Value = customerCheckBillSheet.I_01
},
new SqlParameter("@F_01", SqlDbType.NVarChar)
{
Value = customerCheckBillSheet.F_01
},
new SqlParameter("@IN_DATE1", SqlDbType.Int)
{
Value = CheckBillDate.Month
},
new SqlParameter("@IN_DATE2", SqlDbType.Int)
{
Value = CheckBillDate.Year
},
};
var count = DapperHelper.ExecuteDbfParameter(AppSettingConfig.DbfConnectionString(), CommandType.Text, sqlCmd2, parameters);
return(count);
}